SSIS Packages stored in MSDB on two or more SQL Server instances

This article explains how to administer and maintain SSIS packages, stored in MSDB database, on more than one instance on a single server. First of all I have to mention that SQL Server Integration Services are not instance aware which means that you can have only one Integration Services instance installed on a server. When you start the service in the background it connects to the MSDB database of the default instance (or the primarily installed one) and works in its context. If you want to check what packages are stored in the MSDB (or execute one) on another instance on the same server, you have to follow these steps:

1. Find on your hard drive where the MsDtsSrvr.ini.xml is stored (it is usually in %ProgramFiles%\Miscrosoft SQL Server\<version (80,90 or 100)>\Binn).

2. Open the file and find the xml tag <ServerName>. This is where the instance to which SSIS is connecting is saved. Change it to the instance you want to connect and save the file. This record actually controls to which MSDB database SSIS connects to look for stored packages (and some other stuff).

3. Restart SSIS

This way you will connect to another instance on the same server and be able to check stored packages, administer running ones or executing them.

Note 1: when deploying from SSIS Deployment Wizard to SQL server,or saving packages generated from Import/Export Wizard to SQL server, you can choose instance to which to deploy/save. The process of deployment/saving is actually adding records in the MSDB database of the instance deployed to. This is why you have to be careful or otherwise it would be quite difficult to work with packages saved in the MSDB database.

Note 2: the same is not true when you deploy packages to the File system. If you inspect the configuration file deeply, you will find that it relates to a system folder and have no connection with instance’s MSDB database. So this configuration file is not relative to packages stored on the File system.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s