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

Edholm

This doesn't sound like you should have 2 data connections, but rather environmental and project variables that change depending on the environment you're running on. Then the data connection itself point to a different server depending on your environment. – Entertainer

Thanks! Although I did not understand your answer (I am new to SSIS), I researched environmental and project variables, and found the detailed solution here: mssqltips.com/sqlservertip/4810/… Thanks for pointing me in the right direction :) – Edholm

@Edholm if you solved the issue then write your own answer or accept the current one – Candlepower

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

Measly

Thanks for the help! This link did it for me, as it was easy to understand, very detailed, and explained exactly what I was looking for: mssqltips.com/sqlservertip/4810/… – Edholm

Answer 2

The answer is detailed in the link I found (posted at the end), and explains step by step how to do what I wanted, which was to deploy an SSIS package to different servers by making the connection string dynamic:

• Setup Environment Variables in SQL Server Integration Services

It is basically to follow 5 steps (again, detailed in the link):

1. Create a project parameter and configuring the project's connection manager with the project parameter
2. Deploy the project to the SSIS catalog in SQL server
3. Create an environment in the SSIS catalog, along with a variable
4. Link the environment to the project and the variable with the parameter
5. Execure the package using the environment

Edholm