DTS Lookup

The Lookup object provides the ability to perform queries using one or more named, parameterized query strings that allow a custom transformation to retrieve data from locations other than the immediate source or destination row being transformed. Lookup objects are contained in the Lookups collection of DataPumpTask and DataDrivenQueryTask objects.

An advantage of the lookup feature is that it can be invoked from inside the transformation function. You associate a query and a connection with the lookup. Although you can achieve similar functionality using COM objects, VARIANTs, and global variables, the advantage of the Lookup object is that it uses an established connection that is optimized for quick data retrieval.

In this example, Lookup object execution returns the au_id associated with the DTSSource au_lname:

au_id = DTSLookups("Lookup1").Execute(DTSSource("au_lname"))

DTSDestination("au_id") = au_id

At the package level, the Lookup object (in the Lookups collection of the DataPumpTask or DataDrivenQueryTask objects) allows specification of:

• The Name property of the Lookup object, for collection indexing. There may be more than one Lookup object for a DataPumpTask or DataDrivenQueryTask object.
• The ConnectionID property of the Connection object which will be used. This may be the Source or the Destination connection, if the provider supports this.
• The parameterized query specification. Multiple columns may be in either the key or the returned data values. Note that not all providers support output parameters; therefore, the output is the result set.
• The maximum number of rows to cache. The DTS Data Pump caches up to that number of key/data value combinations and retrieves from that cache if the data is there, resulting in improved performance. If the maximum number of key/data values is exceeded, then the last rowset is swapped out to make room for a new one.

See Also