SSIS connection used based on passed value

Question

I have an SSIS package that populates a reporting warehouse from a live and test environment.

Currently the package has each step duplicated as the source connection changes between live and test. This is a pain to maintain if changes are required.

I know I can send a variable value to the package via the agent job.

I was wondering if I could cut the package down so that the steps only appear once. The agent job could then have two steps that execute the package. E.g First step execute the package and variable set is the name of the live DB and second step executes the package again but the variable set is the name of the test DB.

Can you dynamically select a connection based on a variable received?

Is there a better method?

Thanks

Answer 1

You want to incorporate Environment Variables into your SSIS Packages. Setup the agent job to use different environment variables.

You can set the variables at each step in your agent job. If you create multiple SSISDB Environments in your project folder they can be use or store different variables for executing your SSIS Packages.

Reference: https://www.mssqltips.com/sqlservertip/4810/setup-environment-variables-in-sql-server-integration-services/

Answer 2

Ok so yes you can as I have figured it out.

Created a variable in the package called DB and datatype as string.

Created a connection in connection manager but leave the db blank.

On the connection properties select "Expressions". In the popup dialog box select InitialCatalog from the dropdown under "Property" and put @[User::DB] as the "Expression" value.

On the agent job set the "Property Path" on the "set values" tab as \Package.Variables[User::DB].Value and set the "value" as the name of the live DB.

Then create a second step and copy step above but set value as the test DB name.

Many thanks