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?
Best Answer
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.