DTS Environment Control

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Joe Horton (horj235)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 08:30:59 -0700

DTS Environment Control

 

What solutions have other worked up to handle DTS packages and promotions through their different environments?

 

In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The challenge is when you promote your packages up; the connections have to be changed to reflect the new environment. For simple packages it's not too big of deal - but for more complete ones - you can spend an hour carefully changing all your connections. Try promoting a package that imports/exports 200 tables that was created with the wizard - there is a separate connection for every table.

 

I had one solution where we would add an ActiveX script task as the very first step, that would determine what SERVER the package was executing from - and based on that would dynamically change all the connections by looking up the package name and server from a DB. Of course that was hairy to use because when you develop your DTS package and run it, it is running in the context of YOUR machine, despite being saved on a different server. The environment control solution I developed was really only useful when the package was ran as a scheduled job - as then it was truly running as a job on one of the 4 tier environments. The DBA's shot my solution down as it required that I keep the batch login user names and passwords in my control table that was used to change the connections based on the environment the package was running from.

 

The other problem is source control - we use VSS. If I have a package called XYZ that grabs files from an FTP location every morning - my XYZ package for DEV will look different than the XYZ package for TEST. I could pre-pend the environment in front of the package name and have DEV-XYZ, TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add one more step to message the data further or pick up more files within the same package - I would have to make the changes to 4 different version of the package called XYZ - or make it to one and re-save it across the environments and change the connections for each environment.

 

Anyone have better solutions?