This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

  • Article

Looking at Lookups

Tom Moreau

Tom Moreau has seen enough cries for help with Lookups in the Usenet newsgroups that he's decided that it's time again to put pen to paper—or fingers to keyboard, or whatever we call it in the new millennium.

Lookups are used with Data Pumps and Data-Driven Queries (DDQs). As the name implies, they allow you to look up data from a given data connection based upon a value or set of values that you give it. You can have as many Lookups for each Data Pump or DDQ as you like. Each Data Pump or DDQ has its own collection of Lookups associated with it—the DTSLookups collection.

So how do you implement a Lookup? First, you have to bring up the Properties dialog box for your Data Pump or DDQ in the Package Designer (in Enterprise Manager). If you're using SQL Server 7.0's Enterprise Manager, go to the Advanced tab, where you'll find the Lookups button. Click on it and you'll get the Lookups dialog box. If you're using SQL Server 2000's Enterprise Manager, just click on the Lookups tab (see Figure 1). Click on Add, and you'll get a new row in the list box, where you can give it a name.

Here's where it gets interesting. After you name your Lookup, the next item of business is specifying a data connection for your Lookup. This can be any existing data connection, but it must not be one of the two involved in the Data Pump or DDQ. The reason for this is that those connections are already in use during the Data Pump or DDQ. You must have a separate connection to enable the Lookup to query its own data source to retrieve the information during the Data Pump/DDQ.

For example, you could be pumping data from a text file into SQL Server. However, suppose you have codes in an Oracle database that have to be transformed into something that the SQL Server table expects. You can use your Lookup to query the Oracle database to pick up the correct value.

The next column in the list box, "Cache," tells the Lookup the number of rows that it should cache in memory. If you have a lot of rows being pumped, but the number of possible distinct lookup values is small, setting this value to the maximum possible number of distinct rows that the Lookup can retrieve can increase your throughput.

Finally, click on the ellipsis (…) button in the Query column of the list box to fill in the details on your Lookup. This is where the proverbial rubber meets the road. When you click on the ellipsis button, you'll see the Query Designer, where you can build your Lookup query, either through drag-and-drop or by keying in the raw T-SQL yourself. Question marks are used as placeholders, which receive the value(s) you'll later supply through your Data Pump/DDQ.

For example, if you wish to get an author's last name, given his or her au_id, the following code will handle the Lookup for you (I'll show you how to feed a parameter to a Lookup in a moment):

  SELECT au_lname
FROM authors
WHERE au_id = ?

What if you have more than one search criterion? No problem. Here's the code for a Lookup where you feed it the author's first and last names and then retrieve the au_id:

  SELECT au_id
FROM authors
WHERE au_lname = ? AND au_fname = ?

It's important to note here that the order in which the placeholders appear is the same order in which you feed values to the Lookup. In this case, you must give this Lookup the value for au_lname before you provide the value for au_fname.

What about the reverse case—how do you get multiple columns back from the Lookup? Easy. Just specify the columns you want in your Lookup's SQL. Here's what I mean:

  SELECT au_lname, au_fname
FROM authors
WHERE au_id = ?

In this case, I'm asking for the author's last name and first name, based on the au_id.

Using your Lookups

Now that you've built your Lookups, it's time to put them to work. Suppose you have a Data Pump where you're pumping rows from the authors table to a text file. You can provide the author's au_id and output the last name, based on the au_id. Here's the code you'll need inside your Data Pump: 

Function Main()
DTSDestination("au_lname") = _
DTSLookups("Author Name").Execute( _
DTSSource ("au_id").Value)
Main = DTSTransformStat_OK
End Function

How does this work? To use a particular Lookup, you must pick it up from the DTSLookups collection. In this case, you're using the "Author Name" Lookup. You use the Execute method to pick up the return value from the Lookup. You feed the Execute method the value to be searched. In this case, you're searching on the au_id value from the data source column. This is specified as DTSSource ("au_id").Value. The value returned from the Lookup is then fed to the destination column—DTSDestination ("au_lname").

So how do you feed two or more parameters to a Lookup? Just specify each input parameter to the Execute method, separated by commas:

  Function Main()
 DTSDestination("au_id") = _
  DTSLookups ("Author ID").Execute (
   DTSSource ("au_lname").Value, _
   DTSSource ("au_fname").Value)
 Main = DTSTransformStat_OK
End Function

Now for the biggie. What if you want to get more than one piece of information back from your Lookup? The following code shows you how:

  Private AuthorName (2)
Function Main()
 Map (
  DTSLookups("Author Full Name").Execute _
  (DTSSource ("au_id").Value)
 )
 DTSDestination ("au_name") = _
  AuthorName (0) & ", " & AuthorName (1)
 Main = DTSTransformStat_OK
End Function
Private Sub Map (ByVal Lookup)
 AuthorName (0) = Lookup (0)
 AuthorName (1) = Lookup (1)
End Sub

This Transform uses a global array, AuthorName (2), that acts as a buffer for the values returned by the "Author Full Name" Lookup. The Map() subroutine expects an array of length 2 and simply maps the values from the input array into the AuthorName array. Using this technique, you have to execute the Lookup only once to pick up both values. After the Map() subroutine returns, the destination column is built and then fed to DTSDestination ("au_name").

Using Lookups with DDQs

Lookups have a special importance when it comes to DDQs. They can help you make the decision whether to insert or update a row in the destination table. The classic example here is one where you're pumping data and want to update a row if it exists or insert it if it doesn't. You'll need a data connection for your Lookup, a connection that looks at the same server and database as your destination connection. Your Lookup will select the primary key, based on the primary key. For example: 

SELECT au_id
FROM authors
WHERE au_id = ?

At first glance, this might look redundant. However, you'll get a row if it exists and no row if it doesn't. Now all you have to do is check whether the return value from your Lookup is empty, and you can make your insert/update decision:

  If IsEmpty (DTSLookups ("Lookup").Execute _
  (DTSSource ("au_id").Value)) Then
 Main = DTSTransformStat_InsertQuery
Else
 Main = DTSTransformStat_UpdateQuery
End If

Some final notes

Lookups introduce some extra overhead, and therefore you can expect a performance hit for using them. The Lookup's SELECT statement isn't limited to a single table—you can use a join if you like. Remember, though, that you must have a separate data connection for the Lookup, even if you're connecting to the same server and database as either of your source or destination connections. Check out the DTS package that's available in the accompanying Download file, and try it out yourself!

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the December 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.