SQL Server – SSIS Connection Based on Passed Value

jobsscheduled-taskssql serverssis

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

Best Answer

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/

Related Question