Adding DTS Lookups and Global Variables
Use a Lookup object when you need a transformation to look up data in another query rowset through a separate connection.
Global variables provide a means for tasks within a package to exchange data. When using the ExecutePackageTask object, global variables provide a means for tasks to exchange data between packages.
Creating Lookups
To create a Lookup object, use the New method of the Lookups collection of a class-specific task object that uses transformations. Set the appropriate properties. Typically, you use the following properties:
- ConnectionID. This specifies the connection through which the lookup rowset is queried.
- MaxCacheRows. This determines the number of queried rows cached for reuse.
- Query. This specifies the SQL statement that generates the rowset.
Then, Add the Lookup object to the Lookups collection.
Alternatively, you can create and add the Lookup object to the Lookups collection with the AddLookup method.
You access the lookup in the script of a DataPumpTransformScript or DTSTransformScriptProperties2 object through the Execute method of a DTSLookup object, which is the Data Transformation Services (DTS) scripting object model counterpart of the Lookup object. Usually, you refer to the lookup by name from the DTSLookups collection.
For more information about the Lookup object and the Lookups collection, see DTS Lookups in Visual Basic.
Creating and Using Global Variables
To create a global variable in a DTS package prior to package execution, use the New method of the Package2 object GlobalVariables collection. Set the Value property, and then Add the object to the collection.
Alternatively, you can create and add the GlobalVariable object to the GlobalVariables collection with the AddGlobalVariable method.
You need to create GlobalVariable objects before package execution if the ExplicitGlobalVariables property of the Package2 object is set to TRUE. However, if ExplicitGlobalVariables is set to FALSE, you do not need to create GlobalVariable objects. The package automatically creates global variables that do not exist at first reference.
Setting global variables with the ExecuteSQLTask2 object
You can create and assign values to global variables in the ExecuteSQLTask2 object. Specify a list of global variable names with the OutputGlobalVariableNames property. Values from the first row of the rowset generated by the ExecuteSQLTask2 query (specified with the SQLStatement property) are stored in the named global variables. Set the OutputAsRecordset property to store the entire rowset as a disconnected Microsoft® ActiveX® Data Objects (ADO) recordset in the global variable named first in the list.
Using global variables as input parameters
You can use global variables as input parameters for the queries of the DataDrivenQueryTask2, DataPumpTask2, ExecuteSQLTask2 and ParallelDataPumpTask objects. Specify a list of global variable names with the InputGlobalVariableNames property. For more information, see Adding DTS Query Strings.
Exporting global variables to a DTS package
Create and add global variables, as described above, to the GlobalVariables collection of the ExecutePackageTask object to export these global variables to the target package. These global variables are independent of the global variables in the GlobalVariables collection of the calling package. Use the InputGlobalVariableNames property of ExecutePackageTask to specify global variables from the collection of the package that are to be exported.
Referencing global variables in ActiveX scripts
Reference global variables in ActiveX scripts as members of the DTSGlobalVariables collection. For example, in Microsoft Visual Basic® Scripting Edition (VBScript):
DTSGlobalVariables( "GV1" ).Value
If you assign a value to the above expression and GV1 does not exist, and if the package ExplicitGlobalVariables property is not set, GV1 is created.
For more information about the GlobalVariable object and the GlobalVariables collection, see DTS Global Variables in Visual Basic.