Anyone come up with good Environment controls inside DTS to migrate a DTS package from Dev-->Test-->Pre-Prod-->Prod?

What I want is the package to be aware of what environment it lives on - and based on that it knows to use different DB's.

If the package lives on DEV it knows to use DEV DB's whereas if it lives on Production - it knows to use Live Prod data.

Problem DBA faces now when we promote packages he must manually open packages and change connections based on the environment.

I started developing code pulling information from a DB I created and looked up what connections to use based on calling @@ServerName - but it is a struggle to get it to work right. You get different results when developing it, different when it's scheduled - and yet different on a whole different machine.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

__________________
_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

In one environment I've seen, DTS packages are run on the SQL Server they attach to. In this environment, you would have an ALIAS on the SQL Server like "PROD_SERVER" that would point to the production server on the production SQL Server, development from the development SQL Server, etc...

Then, depending on where you RUN the package the correct server will be run against. This technique can also be used with your remote desktop as long as you change the ALIAS (Using the SQL Client Utilities) to point to the server you want to run against.

If you also use this alias in Enterprise Manager, you can also run a specific version of a DTS package depending on which server you have setup in your ALIASes...

Hope this helps!
Jeff