Nice article. A little different than the technique I use but effective all the same.
My table includes the package id as well and holds all the global variables for a package. From DTSRUN, I pass in a set of global variables to set the connection properties for the GlobalVar connection. Then an ExecuteSQL task extracts all the variables to a resultset and an ActiveX Script task dynamically creates them all.
The advantage of this approach is that my global variable table can reside in one location and serve multiple production servers. It also makes for an easy centralised maintenance location.
Also, I see you use a Dynamic properties task to set the Server Name for the GlobalVarsTable connection. What about the database name and login details?
Interesting, but the ini file problem (limited amount of data that can be held) can be got around (IME) by using a number of different sections in the ini file, and keeping each section below the critical size. This helps organisation, too. Ini files can then be held in a fixed location on each server, so the package can be set to look for its file at \\@@servername\dtsini\ or whatever.
Bill.
We use a 2 phsed approach to handle this
each SQL server has a single ini file in the root folder of the System drive. This contains 1 [Section] that contains the datasource=, catalog= (we use integrated security only) to point to a table on a server containing the rest of the information.
That table contains 3 columns to in effect work like an ini file
SectionParameterNameParameterValue
I will be recommending a change though from using the C: Drive to using a DFS root of the form \\DFSROOT\SQLPARAMS\@@SERVERNAME:
You are missing the point... He's not talking about dtsrun.....and what happens in 'YOUR' approach when you have to update the package? What about copying the DTS to another (external) server where you have no access - when you cannot assume that your ActiveX Script will run?
DTSRun from a command line is extremely limited. If you have many variables to pass in to a DTS package, you have to use a script / program.
I wrote an article posted earlier this week about creating the DTS package from scratch using Perl. In the article, I passed in several values from the command line using Getopt::Std. This functionality can be easily replaced by having Perl read a text (ini) file or get values from a database table.
You can also use a script to open an existing DTS package, assign the variables directly, and execute the package containing the dynamic properties task.
There are many ways to skin the DTS portability cat.
Nice job on the article!
Ok, I'm confused, why can't I assume that an ActiveX Script task within the package won't run?
Our DTS packages move from various developer workstations to a QA server, to a test server, to one of 3-4 production servers, all without a hiccup.
