CODEWITHSUNDEEP
sqlvisual-studiot-sqlvisual-studio-2012ssis
Alex Gordon
Alex Gordon

Reputation: 60691

Programmatically updating connections or dynamic connection managers

I will take your guidance for both a 2008 and 2012 environment, as I will need to be upgrading this project.

I have an SSIS 2008 project where I need to initially run the whole data flow into a staging environment, and after all is good, I need to switch every target to go into a production environment:

enter image description here

Each of these controls has another lawyer of data flow complexity. if you drill down to this one for example:

enter image description here

you will see that this control has more to it, and there are components inside of it depending on the different connection managers:

enter image description here

**how do i easily switch between using connections pertaining to one environment vs connections that pertain to another environment in ssis 2008 or 2012? **

Upvotes: 0

Views: 92

Answers (1)

Mike Honey
Mike Honey

Reputation: 15017

I would consolidate the Connection Managers down to a single set - i.e. remove the "Prod" versions. Then I would add SSIS Package Configuration to set the connection details, e.g. via an XML Configuration file for each Connection.

I would have a copy of the Configuration files for each environment e.g. in sub-folders. If they all need to be executed from a single machine I would swap the Configuration files as required.

Depending on your deployment scenario you might prefer the other Configuration options e.g. Environment variables, SQL - I think Config files are the easiest and most obvious to set up.

Upvotes: 2

Related Questions