DTS Environment control

hortoristic · March 31st, 2004, 12:17 PM

Some of our packages are self aware of the servers they are on and know whether to push/pull data based on that. If the package lives on a TEST server â it would know to work with test data whereas once the same DTS package was promoted to production â it would know to work with Prod data with no changes whatsoever to the package.

Using this sort of environment aware control we have been able to promote a single DTS package through each of our 4 tiers (dev, test, pre-prod, prod) without having to change any of the connections inside the DTS.

The solutions I have come up with isn't too terriably elegant - has anyone else came up with a solution to have their DTS packages environment self aware and reconfigures it's connections based on that?

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

jemacc · April 7th, 2004, 09:14 PM

I am interest on how you have done this can you post your information?

hortoristic · April 12th, 2004, 12:30 PM

I have come up with 2 solutions.

1st Solution:
Using a DSN to the DB. For example - our DB is called "WIN" - so our packages always referance a DSN called WIN - however - the DSN on DEV points to DEV, the WIN DSN on TEST points to TEST DB, the DSN called WIN on pre-prod points to pre-prod and the DSN called WIN on prodcution points to prod. This allows one package to be built and promoted up our 4 tiers with no connection changes. The drawbacks are - 1) developing is tricky. When developing on your local box - of course it will use the DSN on your box despite whatever server it is save ond. 2) Using DSN's as connections limits you to some of the higher features in a DTS package such as even using Parameterized queries in some instances. 3) Speed is not good for huge DB's with lots of transfer.

2nd solution:
Using an Environment Control table - I created records of connections. A DTS package, by using it's own name, would pull up the record and determine all the connections properties and using dynamic edit - reconfigure them at the front of the package. So one of the first queries was to determine what environment the package was running on. When the query would run, it would determine that it was living on the TEST environment and pull up all the connection records for that particular package and loop through resetting the connections for TEST. It was important to capture the @@ServerName immidiately in the package because @@ServerName will return the name of the current existing connection which would change as your package executes it's steps using different connections. Major drawback was having to store the user name/password in my control table - not very secure. Having to add a few steps in front of all the packages wasn't too clean. What I really wanted to do was built a component that I could call as the first step in a package - or call a sub-package. But I ran out of time to develop further and am mostly using solution #1.

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