How to make SSIS package dynamically choose the desired connection manager?

Question

I have an SSIS package with a OLE DB connection manager to 'SQL Server 1', as well as flat file managers that come from the D disk of the local machine.

The package basically retrieves data from the D disk of the machine I am using, and through a series of loop containers with SQL tasks, sends it to 'SQL Server 1'. This all works completely fine.

The challenge is that I would like to deploy the exact same package to 'SQL Server 2'. All files, tables, etc are exactly the same in 'SQL Server 2', as well as the files in the D disk of the local machine where 'SQL Server 2' is located. However, I don't know how to do this.

If I change the OLE DB connection manager to 'SQL Server 2', all works perfectly, as the SQL tasks automatically choose the only connection that exists. Nonetheless, the point is to somehow do this dynamically.

This means having both 'SQL Server 1' and 'SQL Server 2' as OLEDB connection managers, and deploying the same SSIS project to both servers (right now it is only deployed to 'SQL Server 1').

I have created, under Project - Properties, a new configuration that has 'SQL Server 2' selected as the Server Name for deployment. Originally, only one existed, which of course has 'SQL Server 1' as the server for deployment. I know this is not enough, but don't know what else to do.

The objective is to deploy the same package to Server1 and Server2, and any help would be greatly appreciated. Thank you

Answer 1

You can use expressions to change the OLEDB connection manager connectionstring property dynamically:

• How can I use a Variable for a OLE DB Connection in SSIS
• SSIS how to set connection string dynamically from a config file

You can add a Variable or Project parameter and pass the connection string on the package execution. For more details you can refer to one of the following articles:

• Parameterizing Database Connection in SQL Server Integration Services
• Parameterizing Connections and Values at Runtime Using SSIS Environment Variables