DTS Lookups in Visual Basic

DTS Lookups in Visual Basic

You can create and configure a Lookup object to look up data in another query rowset through a separate connection.

Here are the basic steps for configuring a Lookup object:

1. Use the New method of the Lookups collection of a DataDrivenQueryTask(2), DataPumpTask(2) or TransformationSet object of a ParallelDataPumpTask object to create the Lookup object.
   
   
2. Use the ConnectionID property to specify the connection, which should be different from the source and destination connections for the task.
   
   
3. Specify the text of a query that returns a rowset (for example, a Select query or stored procedure) with the Query property.

   The query should have one or more parameters denoted by the "?" placeholder. The query should be designed to return a rowset with a single row. Additional rows in the rowset are ignored.

4. Use the Add method of the Lookups collection to add the Lookup object to the collection.

Example

The following code example shows you how to create and configure a Lookup object:

'Declare a lookup object and a class-specific task.
Dim objLookup     As DTS.Lookup
Dim objDataPump   As DTS.DataPumpTask2

. . .
'Define the lookup object.
Set objLookup = objDataPump.Lookups.New("JobDesc")
With objLookup
    .ConnectionID = 2
    .Query = "SELECT job_desc FROM jobs " & vbCrLf
    .Query = .Query & "WHERE job_id = ?"
    .MaxCacheRows = 60
End With
objDataPump.Lookups.Add objLookup

In the Microsoft® ActiveX® script, the lookup must be referenced with the Execute method of an element of the DTSLookups collection, as illustrated in the following code example:

DTSDestination( "JobDesc" ) = _
    DTSLookups( "JobDesc" ).Execute( DTSSource( "job_id" ) )

If the lookup rowset has more than one column, the Execute method returns a Variant array. The script may need to iterate through the array to use multiple values.

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