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?


2 Answers 2


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/

  • 2
    Just an FYI, this is a great approach so long as the Project Deployment Model is being used. If a Package Deployment Model is being used (which is admittedly not as common), environment variables won't be a valid approach. May 13, 2019 at 14:12

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.