Re: DTS Environment Control
From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/23/04
- Next message: Allan Mitchell: "Re: Copy SQL Server Objects Task"
- Previous message: Allan Mitchell: "Re: DTS Environment Control"
- In reply to: DHatheway: "Re: DTS Environment Control"
- Next in thread: Joe Horton: "Re: DTS Environment Control"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Jul 2004 07:53:14 +0100
On thin client implementations this is also an approach I take as well
although aliasing works and is less visible to the server admins <g>
-- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "DHatheway" <dlhatheway@mmm.com.nospam> wrote in message news:OWoWnvDcEHA.596@TK2MSFTNGP11.phx.gbl... We use the Dynamic Properties task for this. Each job starts with a "Set Servers" dynamic properties task that sets the "Data Source" property for each connection and most of the properties are set from an INI file in a location that's got the same filespec on each of our Test/Prod systems. On a test system, the INI file contains a value called "DatabaseServer" that points to the Test database server. On a production system, the INI file contains a value called "DatabaseServer that points to the Production server. When run from the production environment, the production server is accessed and when run from the test environment, the test server is accessed. This works nicely both for WTS terminal sessions and for batch jobs. We've thought about more complicated schemes involving ActiveX script tasks (and we do use those for some things) but for Test/Prod, the Dynamic Properties task seems to be about all we need and it's simple. Actually, there's one more component... We do some Execute Task functions and we also have some batch jobs that don't involve packages. These we mostly control with environment variables and command-line substitution (although we also use INI files for a few things). "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message news:uqvJkDAcEHA.4092@TK2MSFTNGP11.phx.gbl... 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?
- Next message: Allan Mitchell: "Re: Copy SQL Server Objects Task"
- Previous message: Allan Mitchell: "Re: DTS Environment Control"
- In reply to: DHatheway: "Re: DTS Environment Control"
- Next in thread: Joe Horton: "Re: DTS Environment Control"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
