SSIS ETL Question/Option

Question

In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary values/parameters for running.

In our test environment though we will have test and QA on the same physical box, but using separate instances of SQL Server. Feedback I am getting is that since they are on the same physical box, that means one env variable, so we would have to change the env variable depending on whether ETL was running in test or QA. This is less than desirable.

I understand not wanting to hard code anything into the packages but there has to be a way to accomplish such a thing with multiple environments on the same physical box. I can't justify splitting out onto separate physical hardware for each env.

What have others done. Thoughts? What has worked and what has not?

Answer 1

We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like SQLDev and on the QA environment it is something like SQLQA.

If you are not running from jobs (which I highly suggest doing except on dev while doing actual development), yes you have to change the enviroment variable to the correct one. We have created cmd line scripts to do that easily and placed them on our desktops, so that we can easily switch the environment variables.

Answer 2

1) Instead of a Env variable you could use an xml file (dtsconfig) to point to the SQL Server config database for each environment. If you use the built-in SSIS deployment utility you can manually override the connection string at deployment time. Not a great solution, but a solution.

2) If you go the scripting route you may not need to have the env variable at all. You could just override the connection string using /CONN

Example Code: DTEXEC /FILE "C:\example.dtsx" /CONN NameofConnMgr;"SERVER=yourServer;Initial Catalog=Example"

3)Depending on how many jobs you have you could use Set values in the properties of the job step that runs the SSIS package. I know this is how others have handled this.

If you have a lot of jobs you could use T-SQL to add the jobs to each environment and have a variable for the connectionstring depending on the environment you want to create the job for.

Answer 3

As mentioned by @SQLMD, I have also used a .dtsconfig file for this purpose. This has the added benefit of allowing you to modify the configuration without changing the SSIS package itself. The drawback, is that you'll need to specify the same location on each server to hold the configuration file (such as "d:\SSISConfigs\") in the SSIS package configuration.

Here's an article that details how to do this: http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Answer 4

One solution, albeit kinda sucky, is the convert the Agent jobs from SSIS tasks to CMDExec tasks and then call DTExec directly. If you do this you can set the environment variable in the batch file before the package is executed.

Answer 5

Wrap it with a batch file that sets the environment variables and then kicks off the job. This lets you set up the wrapper file on a per-environment basis and hold as many environments on the machine as you want.