Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How to Use Lookups in DTS
By Allan Mitchell
Version 7.0/2000
Level Intermediate
Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7 | Page 8 | Page 9

Using Lookups

When you see people in the newsgroups answering a question with a query and the query includes a question mark then you may be forgiven for thinking that the person who is replying doesn't know what value goes in here. Here we present a different answer and yes you guessed it the solution we present is Lookups. Lookups are really useful when you want to find out values that you can only get at runtime or the value you want is on another server and is difficult to get at.

Where do I find them?

Lookups can be found in the Data Pump task, the Data Driven Query Task and in SQL Server 2000 they can be found in the Execute SQL task, although technically they are called parameters in the Execute SQL task.

Is that all they do Lookup?

The quick answer is No. We think it's a bit of a misnomer. Although you can use them to lookup values you can also use them to INSERT, DELETE or UPDATE. An example of their use may be when we have one source table but two or 3 destination tables. We can have one Data Pump task with two lookups. The lookups would do inserts into two tables and the actual Data Pump would pump data into the third.

What are we going to be doing with them?

Well we're going to be looking at;

1. How do I define them?

2. How do I use them?

When we look at defining them we'll look from the point of view of the Data Pump task and the ExecuteSQL task because their use in the Data Driven Query Task is identical to the Data Pump.

When we look at using them in a Data Pump we'll see how we can retrieve more than one column and then split it out. In the Execute SQL task we'll be looking at how to retrive a recordset into a Global Variable as well as assigning a Global Variable to a parameter.

Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7 | Page 8 | Page 9