DTS Global Variables in Visual Basic

DTS Global Variables in Visual Basic

Global variables that do not exist when first referenced during Data Transformation Services (DTS) package execution are created at that time. Prior to package execution, you can create global variables explicitly by adding a GlobalVariable object to the package.

Here are the basic steps for creating a global variable in a DTS package prior to package execution:

1. Use the New method of the GlobalVariables collection of the Package2 object.
   
   
2. Set the Value property of the created GlobalVariable object to the initial value of the global variable.
   
   
3. Add the object to the package with the Add method of the GlobalVariables collection.

Example

The following code example shows you how to create a global variable named ALuckyName initialized with the string "SevenSevenSeven":

'Declare the package and global variable objects.
Dim objPackage  As DTS.Package2
Dim objGlobal   As DTS.GlobalVariable
. . .
'Define the global variable.
Set objGlobal = objPackage.GlobalVariables.New("ALuckyName")
objGlobal.Value = "SevenSevenSeven"
objPackage.GlobalVariables.Add objGlobal

Alternatively, the AddGlobalVariable method of the GlobalVariables collection creates the GlobalVariable object and adds it to the collection in a single step. However, it does not return a reference to the object. The following code sample shows you how to create global variable ALuckyName using AddGlobalVariable:

'Define the global variable.
objPackage.GlobalVariables.AddGlobalVariable _
        "ALuckyName", "SevenSevenSeven"

After the GlobalVariable object has been added to the GlobalVariables collection, the object variable is no longer needed and can be reused for other objects or set to Nothing to release its reference.