Using Parameterized Queries in DTS

Parameterized queries are SQL queries written for reusability. They contain parameter markers as placeholders for data that will change from execution to execution. In the Data Transformation Services (DTS) tasks that use parameterized queries, the placeholder syntax is a question mark. The following is an example of a parameterized query:

INSERT INTO Account (CustomerID, CompanyName) VALUES (?, ?)

The following DTS tasks make use of parameterized queries:

• Execute SQL task
  
  
• Data Driven Query task
  
  
• Transform Data task

Lookup queries, which you can include in Microsoft® ActiveX® script transformations in a Data Driven Query or Transform Data task, make use of parameters to retrieve information from an additional connection. For more information, see Lookup Queries.

Input Parameters to DTS Tasks

All the tasks above can execute SQL queries written with parameters if the source that the query is running against supports it. You can map variables into the SQL parameters. The Data Driven Query task can bring in data from a text file, global variable, or the source data as input to its parameter set. The Transform Data task can use only global variables as input to source data queries. Lookup queries can use data from a text file, global variable, or other source data fields as input. However, the Execute SQL task can use only global variables as input to its parameterized queries. For more information, see Data Driven Query Task, Transform Data Task, and Lookup Queries.

Output Parameters to DTS Tasks

The Execute SQL task can save the results of a query to a global variable. You can use the task to save the data in several formats. For more information about these formats, see Execute SQL Task.

The Transform Data and Data Driven Query tasks can save query results into a table destination column, or a variable. You can also save data to an array when using a Lookup query.