SSIS configuration options where test & production are on the same server

Question

I'm new to SSIS, my project is still in the early stages, with only dev & test environments on separate servers. The production environment will be on the same server as test (I don't have a say in that!). I have a number of packages that each have an SQL Agent job to schedule them and I need a way to handle package configurations for dev, test & production.

I know I can store 2 versions of the packages in different directories within MSDB on the same server but what is the best way to implement test & production on the same server with the least amount of manual work to deploy to production?

I could deploy the packages to 2 different locations in MSDB and create a job for each package (1 for test, 1 for prod) but how do I set up package configuration so the package itself doesn't need to change between environments?

Answer 1

You have several options:

1. Create a package configuration table as described here and here. This could work well if you are running the packages on the same server.

2. Override the connection settings in the SQL Server Agent Job. This could work well enough, but it can be difficult to manage if every job has to override connection settings.

3. Use the package configuration XML file and run your packages through the agent via DTEXEC. This seems like overkill to me if the packages are on the same server.

4. Use a variable in the package that inspects the store it is running under to determine which database to use. This is a theory that I haven't tested. If I am correct, the advantage of this approach is that you don't need to setup anything outside of the package. The downside is that it may be difficult or impossible to implement, particularly when you have to consider how the package will run locally in BIDS during testing.