Using Global Variables with DTS Packages

When you execute a Data Transformation Services (DTS) package, you can save data or a value from a DTS step to pass on to subsequent steps. For example, you can use the saved data to change the way a subsequent step executes or to dynamically modify a SELECT statement.

When you use global variables with DTS tasks, you can:

Set a global variable to the accounting month-end date, according to the fiscal calendar of the accounting department. You can do this by using a Microsoft ActiveX® Script task to retrieve the values or by setting the global variable to the accounting month-end dates during design time and referencing during package execution. A step inside the package can use that date to determine the existence of a file with that date in its name. If it exists, the step inserts the records from that file into a table.

Alternatively, the global variable can be used in a WHERE clause to determine the specific records to be loaded (for example, only loading up records that were modified on that month-end date).

You can populate multiple global variables with data in one pass with the Execute SQL task. Entire rowsets also can be saved in a single global variable and accessed as a disconnected ActiveX Data Objects (ADO) recordset. For more information, see Execute SQL Task.
You can then use the global variable as an in-memory lookup table. With the Execute SQL task, issue a SELECT statement against a state table and store the results in a global variable. Then, with the Transform Data task, for each source row, call a "StateLookup" function that iterates through the global variable recordset and matches the state code in the source field with the state code in the table. When there is a match, store the full name of the state in the destination column. For more information, see Lookup Queries.

Creating Global Variables

You can create global variables in DTS and assign them values in the following ways:

During design time by using:
- The DTS Package Properties dialog box in DTS Designer.
- The Execute SQL Properties dialog box. For more information, see Execute SQL Task Properties.
Dynamically during package execution by:
- Using an ActiveX script.
- Issuing a dtsrun command prompt utility from the command prompt and using the /A command switch to allocate and initialize global variables.

Scope of Global Variables

Scope refers to the lifetime of the variable reference in memory. The scope depends on where the variables are declared or initialized. Whether a global variable is still accessible after a package has executed depends on how the global variable was created.

A global variable created during design time retains the value it had when the package finished execution, if the package is saved. For example, suppose you create the global variable, "city," and set it to the value of "Boston." During package execution, an ActiveX script changes the value of "Boston" to "Philadelphia." The next time you execute the package, the global variable will contain "Philadelphia," not "Boston." This is useful if you want to query the value of a package global variable after execution.

However, global variables created dynamically in an ActiveX script have two scopes. If they are created above the Function Main(), they are available to all functions in the script. This is the equivalent to module-level scope. If they are declared within a function, they are available only inside that function. This is equivalent to procedure-level scope.

Examples of Using Global Variables in DTS Packages

The following examples show you how to create, set, retrieve, and use the values of global variables in a DTS package by using an ActiveX script.

Creating a Global Variable Dynamically from an ActiveX Script

If a global variable does not exist when the package is run, you can create one dynamically by using an ActiveX script. To create a new global variable called "city" and assign it a value of "Boston", use the following Microsoft Visual Basic® Scripting Edition (VBScript) code:

   DTSGlobalVariables("city").value = "Boston"

You can dynamically create a COM object and store it in a global variable from within an ActiveX script. In the following example, VBScript code is used to create an ADO connection, which can be used by scripts in the package to execute SQL commands and examine ADO recordsets. In this example, a year-to-date sales table containing a Totals field in the pubs database is created:

Function Main()
   dim conn
   set DTSGlobalVariables("MyConn").value = CreateObject("ADODB.Connection")

   set conn = DTSGlobalVariables("MyConn").value
   conn.provider="sqloledb"
   conn.open  "(local)", "sa", ""
   conn.DefaultDatabase = "pubs"
   conn.execute("Create Table YTDSales (Totals int)")
   Main = DTSTaskExecResult_Success
End Function

The following code example shows you how to create the year-to-date sales table using Microsoft JScript®:

function Main()
{
DTSGlobalVariables("MyConn").value = CreateObject("ADODB.Connection");
conn = DTSGlobalVariables("MyConn").value;

conn.open = ("provider = sqloledb; data source = (local);user id = sa");
conn.DefaultDatabase = "pubs";
conn.execute("Create Table YTDSales (Totals int)");

  return(DTSTaskExecResult_Success)
}

Setting the Value of a Global Variable Dynamically from an ActiveX Script

The following ActiveX script code, written in VBScript, sets the value of a global variable named count to 200:

   DTSGlobalVariables("count").value = 200

Getting a Global Variable Dynamically from an ActiveX Script

The following ActiveX script code, written in VBScript, gets the value of a global variable named count and saves the value in a variable named globalCount:

   globalCount = DTSGlobalVariables("count").value

Using a Global Variable Dynamically from an ActiveX Script

The following code concatenates the value of a column containing a file name with a global variable containing the Julian date, and stores the new results in the filename column in a destination table:

   DTSDestination("FileName") = DTSSource("FileName") &    DTSGlobalVariable("julianDate").value