How to Manage SSIS Packages Stored in Multiple SQL Server Database Instances

By:   |   Updated: 2010-07-13   |   Comments (13)   |   Related: > Integration Services Configuration Options


Problem

We have deployed SSIS packages to the default instance of SQL Server. We recently installed a named instance of SQL Server on the same server and would like to manage the SSIS packages stored in both the default and named instances of SQL Server from SQL Server Management Studio. However, after installing the named instance of SQL Server we no longer see the SSIS packages stored in the MSDB database of the default instance of SQL Server. How can we get this working correctly?

Solution

SQL Server Integration Services is not instance-aware; i.e. you can only install a single instance of the Integration Services service on your server. If you try to install Integration Services and it is already installed, you will get a warning message that continuing with the install will upgrade Integration Services.

When you connect to Integration Services using SQL Server Management Studio (SSMS) and expand the Stored Packages node, you will see the following:

connect to Integration Services using SQL Server Management Studio (SSMS) and expand the Stored Packages node

On my test system I have a default instance of SQL Server 2008 installed, then I added a named instance of SQL Server 2008 R2. The Data Collector and Maintenance Plans folders are created by the SSIS installation; none of the SSIS packages I deployed prior to installing SQL Server 2008 R2 are shown. What we would like to see is two MSDB nodes - one for SSIS packages stored in the MSDB database of the default SQL Server instance and a second one for SSIS packages stored in the MSDB database of the named SQL Server instance.

The answer to our problem lies in the MsDtsSrvr.ini.xml file which is stored in the folder %Program Files%\Microsoft SQL Server\100\DTS\Binn for SQL Server 2008. The contents on the file are shown below:

we would like to see two MSDB nodes - one for SSIS packages stored in the MSDB database of the default SQL Server instance and a second one for SSIS packages stored in the MSDB database of the named SQL Server instance

Note the folder with the type SqlServerFolder. The ServerName node value is .\SQL2008R2. This is my SQL Server 2008 R2 named instance that I installed. There is no reference to my SQL Server default instance. To fix this you simply add another folder with a type of SqlServerFolder and specify the ServerName of the default instance of SQL Server 2008 (just a period for the default instance). The revised MsDtsSrvr.ini.xml file is shown below:

Note the folder with the type SqlServerFolder

I also changed the Name node from MSDB to Default and SQL2008R2 so I can easily identify the database instance of the MSDB database. Without changing the Name node both would be MSDB.

One important note - after you change the MsDtsSrvr.ini.xml file, you have to restart the Integration Services service and also refresh the SSMS Object Explorer in order to see your changes. You can restart the Integration Services service by using SQL Server Configuration Manager or the following PowerShell command:

restart-service -name MsDtsServer100

After restarting the Integration Services service and refreshing the SSMS Object Explorer, you can now see both MSDB instances (the SamplePackage deployed to the MSDB database in the default SQL Server instance is now shown):

After restarting the Integration Services service and refreshing the SSMS Object Explorer, you can now see both MSDB instances

Copying or Moving Packages SSIS Packages

You can use SSMS to import an SSIS package from SQL Server, the file system or the package store. To import the SamplePackage from the MSDB database in the default instance of SQL Server to the SQL2008R2 instance, right click on SQL2008R2 in the Object Explorer and select Import Package; fill in the dialog as shown below:

You can use SSMS to import an SSIS package from SQL Server, the file system or the package store

You will now see the SamplePackage deployed to the MSDB database of the SQL2008R2 instance of SQL Server as shown below:

You will now see the SamplePackage deployed to the MSDB database of the SQL2008R2 instance of SQL Server

You may find that the command line utility DTUTIL.EXE is a more efficient way of copying or moving your SSIS packages. Take a look at our earlier tip Import, Export, Copy and Delete SQL Server Integration Services Packages SSIS for some examples of using DTUTIL.EXE.

Executing SSIS Packages

There are several ways to execute an SSIS package:

  • SQL Server Business Intelligence Development Studio (BIDS)
  • DTEXEC.EXE command line utility
  • DTEXECUI.EXE graphical user interface
  • SQL Server Agent

Keep in mind that when you are executing packages deployed to SQL Server, you are going to have to specify the server where the package is deployed. For example if you are using DTEXECUI.EXE you specify the Server then you can click on the button next to the package name to select the package to run from the list of deployed packages on that server as shown below:

Executing SSIS Packages

Take a look at our earlier tip Different ways to execute a SQL Server SSIS package for the details on the various ways to execute an SSIS package.

Next Steps
  • Keep in mind that when you use DTEXEC to execute SSIS packages deployed to a named instance of SQL Server, you need to specify the /Server option with the server name and instance name. Without the /Server option DTEXEC will try to load the SSIS package from the default instance on the local server.
  • Use DTEXECUI to get the DTEXEC command line to run your SSIS packages. After running your package successfully, you can click on Command Line to get the parameters that you need to append to DTEXEC.
  • The above tip used SQL Server 2008 and SQL Server 2008 R2 as examples. The same applies to SQL Server 2005 except the MsDtsSrvr.ini.xml file is in the folder %Program Files%\Microsoft SQL Server\90\DTS\Binn and the name of the Integration Services service is MsDtsServer.
  • Take a look at Managing Integration Services in Books on Line for additional details on the Integration Services service.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-07-13

Comments For This Article




Saturday, August 10, 2019 - 11:33:27 AM - Raymond Barley Back To Top (82023)

This tip was written for SQL Server 2008 R2. I am assuming you want to execute SSIS packages that are deployed to SQL Server.

Rather than struggle with figuring this out, beginning with SQL Server 2012 there is a project deployment option for SSIS packages. From Visual Studio or SQL Server Data Tools, you convert your SSIS project to the Project Deployment mode. That allows you to simply right-click on the project, select Deploy, and you can deploy your SSIS packages to whatever SQL Server you like. You need to enable the Integration Services Catalog on the SQL Server.

There are quite a few tips available on MSSQLTips.com to help you.

Here are 2 of them:

https://www.mssqltips.com/sqlservertip/2450/ssis-project-deployment-model-in-sql-server-2012-part-1-of-2/

https://www.mssqltips.com/sqlservertip/2451/ssis-project-deployment-model-in-sql-server-2012-part-2-of-2/

Thursday, August 8, 2019 - 4:04:47 PM - José Miguel Manzanares Chirinos Back To Top (82007)

Hi, This post was really helpful. Using your ideas, I managed to get access to the two items mentioned at the start (Data Collector and Maintenance Plans) for my server name folder, but for Default (or as I left mine "MSDB") I still get the same error if I leave the Server Name just as ".". I applied this procedure for the 2 instances of SQL Server: 2012 and 2016. I tweaked it to avoid the error, but I think it basically makes MSDB/Default and "MSSQLSERVER2016" the same, right? At least, that is my best guess. This how my xml file looks like as of now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSSQLSERVER2016</Name>
      <ServerName>MANZANARES\MSSQLSERVER2016</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

Well, I'd appreciate some feedback on this: Is my tweak wrong? Should I simply delete the first SqlServerFolder line?
Again, thank you very much for posting this.

Best Regards,
José Miguel

Thursday, March 22, 2018 - 2:21:07 PM - Melanie Back To Top (75503)

 

 Hi, I hope you can help.  I have tried adding our named instance to the MsDtsSrvr.ini.xml and restarted Integration Services service to no avail.  I continue to get the error that SSIS does not use Named Instance.  PLEASE HELP! 

 

 

Wednesday, August 12, 2015 - 3:37:45 PM - Gerald Britton Back To Top (38434)

Interesting article.  I'll probably use it shortly, if I can get an Export function to complement the Import function you mention.  I am unable to directly import from one server to another because the servers in my situation live in different domains with no trust relationships established.  There is no SQL Server authenticated account for me to use, either.  So, I must export packages from msdb to flat files, then import them from flat files into the new server.

What is missing is an Export function to allow me to export all packages (while autogenerating a file-system folder hierarchy) at once.  I wrote a package that does the job however.

Friday, July 3, 2015 - 6:18:39 AM - Anubhuti Back To Top (38129)

I want to move my deploy package(integration service catalogs) in sql server 2012 to some database. so that I can generate scripts from that deployed package. or is this any way to generate 5ripts from deployed package in integration services catalogs folder.

Tuesday, January 20, 2015 - 6:04:15 AM - stevec Back To Top (35994)

Excellent article ... saved my bacon

 

Cheers,

SteveC.

Sunday, July 13, 2014 - 12:24:25 PM - Eric Back To Top (32678)

This article was a life saver. I have several instances that need to be managed in integration services. Thank you so much!

Thursday, May 8, 2014 - 2:30:37 PM - tim Back To Top (30679)

Thank you Thank you Thank you!  Great article.  Super easy to follow.

Friday, August 30, 2013 - 12:39:30 AM - Anonymous Back To Top (26539)

 

Great Article - clear easy to follow.

Tuesday, June 14, 2011 - 5:54:45 PM - Ray Back To Top (14018)

Thanks for the response and the link. The guidance for SQL 2005 is the same as SQL 2008.  I did refresh the node in SSMS.  I disconnected from SSIS and then Reconnected.  Also, at t one point I stopped SQL Server, SQL Agent, and SSIS and then restarted them all.  I did not successfully deploy to msdb for either. Only to the file system.

I have now installed a 'default' instance of SQL2005 (SP4 CU3) and don't see any change in behavior. I will keep playing around withit.  Just for info here is the core of the config file.  I changed the GT and LT to [ and ].

  [TopLevelFolders]
    [Folder xsi:type="SqlServerFolder"]
      [Name]MSDB_Default[/Name]
      [ServerName].[/ServerName]
    [/Folder]
    [Folder xsi:type="SqlServerFolder"]
      [Name]MSDB_SQL2005[/Name]
      [ServerName]LTCW9L3Q1\SQL2005[/ServerName]
    [/Folder]
    [Folder xsi:type="FileSystemFolder"]
      [Name]File System Default[/Name]
      [StorePath]..\Packages[/StorePath]
    [/Folder]
  [/TopLevelFolders] 

Tuesday, June 14, 2011 - 6:36:28 AM - Raymond Barley Back To Top (14016)

Did you refresh the node in the SSMS object browser?

The tip was for SQL Server 2008; you're using SQL Server 2005.  Take a look at http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx which is how to configure the SQL Server 2005 SSIS service.  Maybe there is something different.

Have you successfully deployed packages to the named MSDB instance?

Monday, June 13, 2011 - 6:46:40 PM - Ray Back To Top (14012)

Hi Ray;)

I have been working through your example and I must be doing something wrong because I don't see any changes.  I have a named instance of SQL 2005 (SP4) installed.  I don't have a default instance and I don't have any other named instances.  This named instance was my initial install and I have not un-installed anything.

I have tried adding the new sqlServer item per your example with no luck. I have also tried just changing the name of the existing nodes to "File System Default" and "MSDB Default" with no new nodes.  No Luck.  I have tried stopping a starting SSIS, the named instance, SQL Agent, etc.   No Luck.  The nodes in the Object browser remain unchanged.

Sigh.

Thursday, September 2, 2010 - 4:29:00 AM - R.Vinothraja Back To Top (10108)
Hi Friends,

Pls solve my following query with example.

now i am doing project using ssis 2005. in that

1. import data from multiple resources like .xls, .xml,db

2.Read only file path from created meta data table like dil_table_met (Filetype=.xls,filepath=c:\..)

3.In that meta table, i want to read only file path and check file path which filetype like .xls or .xml and then import to excel source and to db destination , the same via for all file type

4. next read data from resources import correct data into correct table and wrong data into error table

5.for all these above condition are in loop.

 

pls give a example for this its very urgent

pls pls help me out for this














get free sql tips
agree to terms