Using Data Transformation Services: Using DTS Lookups, Introduction

This tutorial's main objective is to give a clear and concise explanation of how to use the Lookup query feature in Data Transformation Services. Query reusability and parametric (parameterized) queries are at the heart of DTS lookups. In parametric queries, place holders are positioned in a query for the parameters, and depending on requirements, these change from execution to execution. A familiar example in a local video store is your telephone number, which looks up your account. Here is an example of a parametric query that looks up the contact information from a table, given the phone number (assuming it is unique):

Select fname, lname, address, city, state
from Contacts
where phone=?

It is assumed that the table 'Contacts' may contain several other fields besides the ones selected, including 'phone' numbers. Most database products have procedures either via code, or via user interface, for creating parametric queries.

The DTS lookup queries can allow results from the query to populate the destination table using an existing source connection, or from a different connection. Sometimes using a different connection may speed up the process. Not only Select statements can be used in DTS lookup queries, but other data modification queries can be used as well. In this tutorial only the Select queries are considered.

The DTS Object model or the DTS designer can be used to create and manage the lookup queries. The lookup queries can come in very handy for looking up tabular information, parallel updates to databases, input data validation, calling up stored procedures, and so on. One of the main requirements for successful parameterized query usage is that the data source connection should support the parametric representation in a query.

In this tutorial, the emphasis will be on the use of DTS Designer. The DTS object model will be considered in a future tutorial. The lookup queries considered in this tutorial are invoked from within a Transform Data Task discussed in the previous DTS tutorials. The main objective is to provide a step-by-step procedure with adequate explanation. In this tutorial the pubs example database from MS SQL 2000 server is used.

This is the most basic example of a parameterized query. In a table containing, first names and last names, it may be required to find a last name, given the first name.

SELECT     au_lname  'What is last name of a person?
FROM         authors  'in the author's listing
WHERE     (au_fname = ?) 'whose first name is such and such.

In this next example, you are given the last name of the author and the name of the state in which the author resides, and you must find the title of the books he has written. In order to access such data you may need to make 'joins' as shown in the script. You may also notice two place holders represented by the ? mark.

SELECT     titles.title
FROM         authors INNER JOIN
titleauthor ON authors.au_id = titleauthor.au_id

INNER JOIN
titles ON titleauthor.title_id = titles.title_id
WHERE     (authors.au_lname = ?) AND (authors.state = ?)

Multiple arguments example

We first create a package, called NewLookups as shown here. The procedure is described in detail in earlier tutorials.

MS Access database and SQL 2000 Server database are the two connections used for this tutorial. These are shown in the next two pictures.

The following query selects some columns from the SQL 2000 Server and, using the DTS, populates a destination table.

SELECT     authors.au_lname, authors.phone, authors.state, 
authors.zip, titles.title, titles.price
FROM   authors INNER JOIN
titleauthor ON authors.au_id = titleauthor.au_id 
INNER JOIN
titles ON titleauthor.title_id = titles.title_id

The transformation has not only populated the destination table with the selected columns from the source, but also extracted the field 'title' from the lookup query against the source to populate the destination table. The following Transform Data Task has such a property. Although this is a finished product, the following sequence may help you understand.

Since we need to get the 'title' field from the lookup query, we click on the tab lookups which brings up the next window. In the first column, you may give any meaningful name to your query (here, it is GetTitle). in the next column, you need to pick a connection; for this example the same SQL Server is used. It is possible, and sometimes advisable, to create another connection. It is also possible to cache the results -- not configured in the present case. Now the query itself needs to be coded using the user interface query designer. This is invoked by clicking on the ellipsis button in the query column.

Example continued

On clicking the ellipsis button the following query designer pops up. Herein, the previously mentioned query is configured as shown. The details of how this is carried out have already been discussed in previous DTS tutorials. This is the same query we started with in the beginning.

The 'Source' and 'Destination' needed for this transformation are shown here.

Now in the above windows, accessing the General tab leads to the Activex scripting window shown here. The picture is not clear, but the package objects can be seen in the left pane.

The ActiveX script that makes this transformation is shown in the next paragraph. The variable iTitle is assigned the value returned by the lookup query, "GetTitle" by passing the two arguments au_lname and state. The syntax is: returnValue=DTSLookups().Execute(param1, param2). This is then passed to the DTS Destination.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
'  Copy each source column to the destination column
Function Main()
iTitle=DTSLookups("GetTitle").Execute(DTSSource("au_lname") ,

DTSSource("state"))
DTSDestination("title")=iTitle
Main = DTSTransformStat_OK
End Function
'************************************************************************

As discussed in previous tutorials, it can be parsed and tested in the Activex designer interface. This completes the Transform Data task and results in the previous window shown earlier (the finished product!). When this package is run, the MS Access database table gets populated as shown in the next picture, which is the result required.

DTS Lookup returning multiple values

In this next example, you are given the last name of the author. You are now required to find the author's full name (a combination of first name and last name), and the his state of residence. You may also notice that there is only one placeholder ? mark.

SELECT     state, au_fname + au_lname AS ' fullname'
FROM         authors
WHERE     (au_lname = ?)

A similar sequence of steps is used in this example as well. Only some critical steps are shown here. In this case according to the above sql script, we use one source parameter, au_lname and generate the state and fullname from the query which, in its finished form, would look like this.

The query needed to obtain the input/output of the above is provided by creating the lookup query as shown here.

Now, the ActiveX script that makes the transformation is shown here. The left hand pane shows the package details, where the source, the destination, and the lookups are all shown. The script is also shown below the picture.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
'  Copy each source column to the destination column
Function Main()
IFname=DTSLookups("Multi").Execute(DTSSource("au_lname"))
DTSDestination("fullname")= IFname(1)
DTSDestination("state")= IFname(0)
Main = DTSTransformStat_OK
End Function
'***********************************************************************

In the above script, the variable IFname is an array, holding the values the query returns in the array, accessed by the index. For example, IFname(0) holds the "state", IFname(1) holds the "fullname", and so on. This completes the package design and it can be executed. The result of this execution is shown in the next picture.

Summary

In this tutorial, two examples of using DTS lookups were described, one of them in detail. While the examples use one of the existing connections, they could have used a new connection. Although an example of a select query was shown, they could also be used in queries that modify data. The steps are quite detailed, and it is relatively easy to implement in the designer, once the syntax is correctly understood. However, the underlying connection should support the parametric representation. For example, this would not work with text files.