Configuring a Simple Lookup Query

To configure a simple lookup query, complete the following steps:

1. Create a new connection.

   Although a lookup query can share a connection with the source or destination under certain conditions, the best performance occurs when it is given its own connection.

2. Create and name your parameterized query.

   The parameterized query can be a stored procedure invocation or a SELECT, INSERT, DELETE, or UPDATE statement. Mark parameters by including question marks in place of expressions that will be set at runtime. Parameter values typically come from source data but may be supplied by global variables or any other terms accessible to Microsoft® ActiveX® script code.

3. Create an ActiveX Script transformation with code to execute your query.

   The query is executed with the following statement:

   return value = DTSLookups("query name").Execute(argument list)

   where return value is a variant that receives the result of the query, query name is the name you provided the query in step two, and argument list is a comma-separated list of parameter values, one for each question mark in the query.

Before you configure a lookup query, consider the following:

• Tasks that avoid lookup queries run much more quickly than those that use them. For example:
  • A task that joins two tables in a source SQL query runs faster than one that looks up the information from the second table.
    
    
  • Two separate Transform Data tasks usually can be run in less time than one that inserts data in a second table with a lookup query.
• In general you should use a lookup query only in situations where there is no alternative. For example:
  • A source join is impossible because data resides in non-SQL Server™ databases, or the volume of data generated by a source join would exceed system capacity.
    
    
  • A stored procedure must be called, or a DELETE or UPDATE query must be run.
    
    
  • The need for clarity outweighs any performance issues.

Lookup Query Example

In this example, you have source data that includes a postal code but no city. A Mail Codes table contains a row for each postal code and a column for city name. The procedure for including this city name in your destination rows is as follows:

1. Create and name a connection to the database containing the Mail Codes table.
   
   
2. Write your query statement, leaving a question mark in place of the postal code value:

   SELECT City FROM MailCodes WHERE PostalCode = ?
   

   When you configure the lookup query, you are required to provide a connection name and a query name. Use the connection you created in step one, and name your query GetCity.

3. In an ActiveX Script transformation, include the following code to execute your query and place the resulting city name in the destination row:

   DTSDestination("City") =     DTSLookups("GetCity").Execute(DTSSource("PostalCode"))
   

   The postal code is drawn from the source row. Its value replaces the question mark each time the query is executed.

Using More Than One Argument

Sometimes a lookup query takes more than one argument. For example, when:

• Required information has a multiple column key.
  
  
• An INSERT or UPDATE statement must fill multiple columns.

To configure a lookup query with multiple arguments, you must:

• Include multiple parameters in your query statement.
  
  
• Provide values for each parameter when you execute the query.

In this example, you need to retrieve a city name, given the postal code and country. The GetInternationalCity query has two parameters:

SELECT City FROM MailCodes WHERE PostalCode = ? AND Country = ?

In your ActiveX script, values are provided for the postal code and the country:

DTSDestination(City) = DTSLookups("GetInternationalCity").Execute _
   (DTSSource("PostalCode"), DTSSource("Country"))

Looking Up More Than One Value

Sometimes you want to retrieve multiple values with a single lookup (for example, when you have a customer account number and need a name and address).

Data Transformation Services (DTS) handles multiple columns in query results by returning an array of variants. Each entry in the array holds one result value. The index of the first value is 0.

In this example, you need to retrieve a city and a region, given the postal code. The GetCityAndRegion query selects both required columns:

SELECT City, Region FROM MailCodes WHERE PostalCode = ?

The returned values are accessed through the following ActiveX script code:

dim varArray
varArray = DTSLookups("GetCityAndRegion").Execute(DTSSource("PostalCode"))
DTSDestination("City") = varArray(0)
DTSDestination("Region") = varArray(1)

See Also

Data Driven Query Task

DTS Connections

Transform Data Task

Using ActiveX Scripts in DTS

Using Parameterized Queries in DTS