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?