INF: How to Use Lookups in Data Transformation Services (243775)

SUMMARY

A lookup is a query that is executed for every row in a transformation. Because a lookup is called for every row, the use of a lookup might have some performance impact such as:

If a lookup is called from an ActiveX Script, you reduce the transform speed by the use of the ActiveX script.
Performance is further reduced by the overhead in the lookup itself.

Therefore, for optimal performance do not use lookups unless you must. Other methods are better suited to the transformation that offer better performance as well.

MORE INFORMATION

Work the lookup into your source query. If the source is a file, consider using a bulk insert into a staging table, and then use a query on the table to avoid using the lookup.

How to Improve Lookup Speed

Lookups have their own cache. If the cardinality of the data is low it will help performance significantly. Use a separate connection for the lookup, which keeps the lookup from interrupting the source and destination.

Technical Bits

Lookups are parameterized queries, and they can use multiple parameters. A lookup can also return multiple values (into a zero based array). Only the first row of the result set is returned. A lookup does not have to return any results and you can use it to perform inserts, updates or deletes.

Lookup Examples

A simple lookup:

Select au_lname from authors where au_id = ? DTSDestination("au_lname") =
DTSLookups("Lastname").Execute(DTSSource("au_id"))

A more complex lookup:

Select au_lname, au_fname from authors where au_id = ? and state =?
Dim rTest
rTest = DTSLookups("Lastname").Execute(DTSSource("au_id"), _ 
DTSSource("state"))

DTSDestination("au_lname") = rTest(0)

DTSDestination("au_fname") = rTest(1)

User Scenarios

Row Level Restartability

Finding a surrogate key (where a query cannot). A data warehouse dimension table may have a separate identity that is not related to anything. When you perform an insert into the fact table you may have to perform a lookup on the values in the dimension table to return the key for the fact table.

242543 INF: DTS Row Level Restartability After an Unexpected Failure