DTSrun, DTS, and DTS global variables revisited

Although we're in a new era with SSIS, many of us still work extensively with SQL 2000, including myself. An all too familiar problem was recently echoed at a new clients office whereby several customers receive data dumps via named text files. Rather than have 30 dts packages each with their respective client_filename.txt hardcoded, obviously it would be much cleaner to have 1 dts package and take advantage of the global variable functionality within SQL Server. The problem for me has been getting this thing to work correctly. If this subject is old news, you probably don't have any interest in this particular read; for me I've never really needed it too much before, so revisiting the subject seemed to be the order of the day. After geeking with for a bit, I have figured it out so I want to pass this info along to you.

DTS run is executed via the command line syntax:

C:\>dtsrun
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):

Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also specified)
/~R Repository Database Name

Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value
/~L Log file name


Optionally you can fire this off in TSQL as well, but for here we'll assume the command line syntax. The options /A, global variable, is the feature that I needed to tap into in order to change the name of the files for each of the 30 customers. Lets translate this command that includes a global variable

Example:

C:\>DTSRUN /S /NPASS_GLOBAL /E /AVAR1:3=30 /AVAR2:8="c:\test2.txt"

I am calling DTSRUN using the local server (hence, /S), the package name, trusted connection, and two global variables. Note that I have not tested the maximum number of variables but would assume that it's probably many. The first thing to note is that I have all of my syntax "butted" together. This is optional, just looks nicer to me. Secondly, the variable names are CASE SENSITIVE! This is a biggie - if they are not your command will run without error, yet return no results! Third, the data types must be in numeric format. Here are the data types that I've found:

TABLE 1: Common TypeIDs That DTS Uses

Data Type TypeID
Integer 3
Date 7
String 8
Boolean 11
Decimal 14
Integer (8-byte) 20

You can research this further in BOL if necessary to see if there are others.

Having completed this successfully, you are now ready to use the global variables in an active x task or sql task, or wherever. For mine, I piped it into a Dynamic Properties Task, then, via the disconnected edit, I changed the global variable to equal the text file for both source/destination name replacement. This worked great, and will now let me run a single dts for n number of clients.

Lee

----------------------------

Originally posted on Friday, April 21, 2006 on another site. Doesn't matter - it's my content anyway.