Using Data Transformation Services: Using DTS Lookups, Introduction

(Page 1 of 4 )

If you have ever wondered how to use the Lookup query feature in data transformation services, this is the article for you. This tutorial introduces DTS lookups, and includes two examples for how to use them, one in extensive detail.

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.

 

DTS Lookup 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.

DTS Lookup with one argument providing one output

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.

Next: DTS Lookup with multiple arguments >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: Using Data Transformation Services: Using DTS Lookups, Introduction

· Most of the articles on DTS written by me including the present one use the...    · Hi,I am pretty new to DTS but not to ETL. One of the major requirements I have...   >>> Post your comment now!