Re: DTS Environment Control

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

From: DHatheway (dlhatheway_at_mmm.com.nospam)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 10:46:56 -0500

When I have many tasks to set, I first use the Dynamic Properties task to set a few package Global Variables based on the .INI file (and, by the way, there's one on my desktop PC that has the "Test" settings in it) and then I write an ActiveX script task that will loop through the package tasks and set the appropriate properties to a value derived from the values in the Global Variables.

You might also have to do a little extra ActiveX scripting to enable/disable tasks dynamically (that way you shouldn't have to draw all those little dependency lines from the prep tasks to the data-transfer tasks). We've got a package or two that does this.

  "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message news:#HHEeizcEHA.712@TK2MSFTNGP11.phx.gbl...
  I could see this approach working well for defining just SQL Source/Destination settings - but what about other sources and destinations? Say for example you had to FTP flat text files over to a Data Warehouse server - your INI files would work well for the SQL boxes - but we need to add entries for each of the flat file connections for EACH table in your INI file as well. If there were 200 tables - you would need 200 entries just for that part of the connections - just for this one package!
    "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

       

      [space-saving snip]