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?