Lookup Queries

Although I don't recommend you use lookup queries extensively, I feel obligated to cover them and explain how they work. Essentially, a lookup query is a parameterized subquery that you can set up to be called to look up data values. (A lookup query can actually do things besides look up data values and perform other types of work for each value in the source data.) Using a lookup query is similar to opening a cursor on a table in T-SQL and calling a stored procedure or executing a subquery for each row in the cursor. You usually execute a lookup query during a transformation of some type. A lookup query can be a stored procedure call or a plain SQL query. A typical lookup query might resemble the following:

SELECT     phone
FROM       authors
WHERE      (au_id = ?)

You set up lookup queries on the Lookups tab of your transformation task. If the task supports lookup queries, you'll see a Lookups tab in the dialog. Each lookup query has a name, a source connection, a cache setting, and a query associated with it. The cache setting allows you to configure the number of values returned by the lookup that are cached for reuse. This is especially useful when you are transforming a relatively large number of rows and the number of rows in the lookup table is relatively small.

You reference lookup queries by using the DTSLookups global function. A typical reference to a lookup query in an ActiveX transformation might look like this:

DTSDestination("phone") =
  DTSLookups("phone").Execute(DTSSource("au_id"))

Here, we pass the DTSLookups function the name of the lookup we want, then call the Execute method on that lookup object, passing it the parameter required by its parameterized query.

It's possible for a lookup query to return zero rows. When that happens, the Execute method will return an empty variant. You can test for this in your script using the VBScript IsEmpty function, as shown in Listing 20.4.

Listing 20.4.

Dim Phone
Phone = DTSLookups("phone").Execute(DTSSource("au_id"))
If IsEmpty(Phone) Then
  DTSDestination("phone")="None"
Else
  DTSDestination("phone")=Phone
End If

It's also possible for a lookup query to return multiple rows. While you only have access to the first row returned, you can use an ORDER BY clause in your lookup query to ensure that the first row returned is the one you want. You can also detect when multiple rows are returned by inspecting the LastRowCount property of the lookup object returned by DTSLookups, as shown in Listing 20.5.

Listing 20.5.

Dim c
c=DTSLookups("phone").LastRowCount
If  c > 1 Then
  MsgBox "Warning: " & c & " lookup matches found"
End If

You can explore lookup queries further by loading into the DTS Designer the LookupQueryExample.DTS package in the CH20 folder on the CD accompanying this book. Double-click the Transform Data task and check out its Lookups page. In this example, we copy the authors listed in the pubs..titleauthor table to a new table in the Northwind database. We take the au_id column from titleauthor and pass it to a series of lookup queries to retrieve various columns from the authors table. No columns from titleauthor are actually copied to the destination except for the au_id column.