January 18, 2006 at 11:20 am
Hi,
Problem: Insolate production tables from DTS packages on test server
Our SQL 2000 production environment is a data warehouse that uses DTS jobs to read text files to populate it. The test environment is a restore of a .BAK from production of one of our databases on a seperate server on the same network domain as our production server. I brought the DTS packages over from production by copying the sysDTSPackages table to test.
After the packages landed on the test server, I changed the connection properties and file paths of the data sources for the packages we are using to test a new process. My worry is that If I overlook a connection the destination table will be hit in production.
I've changed the account the SQLAgent runs on so if the packages are executed from a job, they fail if the destination is a production table.
My problem is that I can't figure out how to insolate the destination tables when a package is executed in the DTS designer environment.
Thank you for your time and consideration.
andy
January 18, 2006 at 12:45 pm
Some (not all) recommendations:
1 You may save packages as Visual basic files and search for the production server string if it completely absent. There will be text like this:
oConnection.ConnectionProperties("Data Source") = "MyServerName"
2. If the source are only text files on a local machine then plug off the network cable on the test server and run each package to make sure it would not give you an error message that it is trying to connect to the production server.
3. (Wild!) put an entry in HOSTS file on the Test server (ask your sysadmin first!)
127.0.0.1 ProductionServerName
This will re-direct connections to productionServerName to the local host (test server) I just tested it, it works! The only thing that happened when looped back to the test server it lost my Windows credentials, so I had to specify SQL Credentials for the connection.
Regards,Yelena Varsha
January 18, 2006 at 2:21 pm
There have been some good articles on this issue (though in SSIS, not DTS) in Jamie Thompson's blog. "Keeping your packages in the dark". However, the concept is equally applicable to DTS and SSIS.
In essence, every DTS package should have an initial step that configures all connection objects in the package based off a local configuration file.
The package is designed to be "kept in the dark" about its environment, until runtime when it reads its config file and only then do connection properties get set to the current environment. This yields very portable packages where they can be migrated from Dev->QA->Production with no worries about where the package connections have been hard-coded to point to.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply