Using Data Transformation Services: Using DTS Lookups, Introduction - DTS Lookup with multiple arguments
(Page 2 of 4 )
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.
Next: Example continued >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 20 
