Manage SSIS packages saved in multiple instances of SQL Server | SansSQL

Monday, August 26, 2013

Manage SSIS packages saved in multiple instances of SQL Server

Consider a situation where you have multiple instances of SQL Server installed on a single server and each instance has multiple SSIS packages store within the msdb.

Also we know that the SQL Server Integration services is not instance aware which means we cannot install more than one instance of SSIS on one server.

Now the question is how can we manage SSIS packages stored on the different SQL Server instances?

The Solution for this is very simple and can be achieved by modifying the SQL Server integration services configuration file to include the second instance. By default, the configuration file will include the SQL Server instance which was installed first and we have to add the additional SQL Server instances to this config file.
The name of the config file is "MsDtsSrvr.ini.xml" and will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before modifying the file

Now for this file, I will add one more SQL Server instance so that I can manage SSIS packages from 2 SQL Server instance with a single instance of integration services.
After modifying the file we need to restart the SQL Server integration server services in order to reflect the changes made.

No comments:

Post a Comment