Global Variables

Global variables are used in situations where you need to pass data or object references between several different Microsoft® ActiveX® scripts in a single package. You can create global variables in several different ways.

Using the DTS Package Properties Dialog Box

The Variables table in the Global Variables tab of the DTS Package Properties Dialog Box allows you to enter global variable names and initial values.

For example, you can define a global variable named counter with an initial value of 0, and, in an ActiveX script, use it to test conditions such as the number of retries of a connection. After a connection is made, you use an ActiveX script to reset the value of counter to 0.

Any time a step containing a global variable is executed, the value for the global variable is updated in the DTS Package Properties dialog box.

Setting and Getting a Global Variable

The following two lines of ActiveX script code, written in Microsoft Visual Basic® Scripting Edition, get and set a global variable:

globalVal = DTSGlobalVariables("global").Value
DTSGlobalVariables("myglobalVar").Value = 200


The following ActiveX transformation script, written in Microsoft Visual Basic Scripting Edition, accesses a global variable:

function main
        DTSDestination("DestColumn") = DTSSource("SourceColumn") &
                DTSGlobalVariable("myglobalVar").Value
end function

Creating a Global Variable Dynamically from Within an ActiveX Script

You can dynamically create a global COM object from within an ActiveX script in the DTS Designer. In the following example, Visual Basic Scripting Edition code is used to create an ADO connection, which can be used by several different scripts in the package to execute SQL commands and examine ADO recordsets:

DTSGlobalVariables("adoConnection").Value =
        CreateObject("adoConnection")


COM objects, such as the ADO connection shown here, can be referenced by ActiveX scripts:

function main
dim cnn1
        set cnn1 = DTSGlobalVariables("adoConnection").value
        cnn1.provider = "sqloledb"
        cnn1.open "(local)", "sa", ""
        . . .
end function

This example demonstrates how to create and manipulate a global ADO connection object using Microsoft JScript®:

function Main()
{
        DTSGlobalVariables("adoConn").Value  = CreateObject("ADODB.Connection");

        conn = DTSGlobalVariables("adoConn").value
        conn.open("provider = sqloledb;data source = (local);user id = sa");
        conn.DefaultDatabase = "tempdb";

        conn.execute( "Create Table MyGlobalVariable (c1 int) " );

        return(DTSTaskExecResult_Success);
}


See Also

DTS Package Properties