Multiple SQL Server Integration Services (SSIS) database instances on one machine

On a recent assignment they requested me to implement two Integration Services instances on the same machine. Their serverpark isn’t that big, but they want to simulate a separated test en development environment. With DTS this wasn’t a real problem, but how do you configure SSIS to use multiple instances, since SSIS can only be installed once on a single machine and totally works different then DTS. Before I explain how you can configure SSIS you first need to know the differences between DTS and SSIS. 

Traditional (DTS) warehouse loading

  • Integration process simply conforms data and loads the database server
  • The database performs aggregations, sorting and other operations
  • Database competes for resources from user queries
  • This solution does not scale very well

 

Warehouse loading with SSIS

  • SQL Server Integration Services conforms the data
  • But also aggregates and sorts, and loads the database
  • This frees-up the database server for user queries

SSIS includes a configuration file for configuring the Integration Services service. By default, the file is located in the folder, Program FilesMicrosoft SQL Server90DTSBinn, and the file name is MsDtsSrvr.ini.xml.

The default configuration file contains the following settings:

  • The root folders to display for Integration Services in Object Explorer of SQL Server Management Studio are the MSDB and File System folders.
  • The packages in the file system that the Integration Services service manages are located in %Program Files%Microsoft SQL Server90DTSPackages.

You can modify the configuration file to display additional root folders in Object Explorer, or to specify a different folder or additional folders in the file system to be managed by Integration Services service. The example below shows how I configured the configuration file to use more than one MSDB database, which are stored in separated database instances.

As you can see in the Management Studio I can store my packages in different databases as well a multiple File Systems.

Note
The registry key HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTSServiceConfigFile specifies the location and name for the configuration file that Integration Services service uses. The default value of the registry key is C:Program FilesMicrosoft SQL Server90DTSBinn MsDtsSrvr.ini.xml. You can update the value of the registry key to use a different name and location for the configuration file.

Summary
The SQL Team did a great job for letting you choose how many instances you want to use on one machine. They’re fully configurable and administrable and you’re able to create a real test and development environment on one machine. Great job guys.

 

4 thoughts on “Multiple SQL Server Integration Services (SSIS) database instances on one machine”

  1. I have my servers set up this way but how do I use the Connection Managers expression to know which instance to use when running the package?

  2. I have my servers set up this way but how do I use the Connection Managers expression to know which instance to use when running the package?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>