Same set of SSIS packages concurrent on the same machine connecting to different databases.

My customer asks me to run the same set of SSIS packages concurrent on the same machine connecting to different databases.

In plain English this means the following, ‘instance 1’ of the set of packages connects to database A and in parallel ‘instance 2’ connects to database B.

This is an easy one, you would think? Indead, simply specify a different configuration file when you call the package from the SQL Agent. It can be a .dtsConfig file in a directory on the c-drive (or other drive), or use an environment variable. Provided you have specified in the package configuration that the connection string from the ‘Connection manager’ comes from a .dtsConfig.

It works fine. Though if you look closer, you will discover that the master package does not pass the connection to the packages it calls via an 'Execute Package Task'. So the connection manager in the  underlying packages will still use the connection that was specified at design time in BIDS.

To solve this it is better to create a variable of data type string at package level and let this value come from a .dtsConfig. This value must be a valid connection string. Then on the connection manager you have to add an expression to overwrite the connection string with the value from this variable. In many cases its interesting to set the propertie 'DelayValidation' to true, to make sure the connection string is not used before it is overruled by the expression.

In the underlying package you must create a variable with the same name and in the ‘Package configurations organizer’ you have to specify its value is coming from the variable with the same name in the parent package. Here also you have to specify on the connection manager via an expression that connection string will come from the value from this variable.

Now here also overwrite the connection manager his connection string via an expression to get the value from the value from this variable.