Lookup Queries
A feature of the Transform Data and Data Driven Query tasks, lookup queries allow you to run queries and stored procedures against other connections besides the source and destination. For example, by using a lookup query, you can make a separate connection during a query and include data from that connection in the destination table.
Lookup queries allow you to customize and execute an SQL statement from within a Microsoft® ActiveX® script transformation. The statement may be a stored procedure invocation, or a SELECT, INSERT, UPDATE, or DELETE statement. You customize these statements for each source row through the use of parameters, blanks left in the statement to be filled in before execution. When you execute a lookup query, your script provides values to be substituted for each parameter. Your results can be loaded into destination columns or can serve as input for further script processing.
You can use lookup queries to:
- Look up tabular information.
- Perform parallel updates on two database systems.
- Validate input data before loading it.
- Invoke stored procedures in response to input conditions.
- Use global variable values as query parameters.
You can use either Data Transformation Services (DTS) Designer or the DTS object model to create and manage lookup queries. For more information about programming with lookup queries, see Adding DTS Lookups and Global Variables and Lookup Object (DTS).