Building a Data Driven Query
Although you can use Data Driven Query tasks to build data driven queries that vary in complexity, you follow the same basic steps for building a data driven query in all cases.
The following examples show how to build data driven queries, one simple and one complex, using the Data Driven Query task in Data Transformation Services (DTS) Designer. In the first example, every source row leads to the update of a destination row. Only one query type, an Update query, is used. In the second example, multiple query types, both an Update and a Delete query, are used.
To update a destination row using a single query type
- Define the source data.
The data can be a table or the results of a SELECT statement, parameterized SELECT statement, or stored procedure. You choose the data source on the Source tab of the Data Driven Query Task Properties dialog box.
- Define a binding table.
This binding table allows you to transform source data before it appears in your SQL statements. The Data Driven Query task makes no actual changes to the destination unless you specifically request them in your queries. The binding table must be an existing table. This table is used to define the schema for binding the parameters. The columns of this binding table must match those of the parameterized queries that will be written against it (Step 5) in type. You declare the binding table on the Bindings tab of the Data Driven Query Task Properties dialog box.
- Provide the decision-making logic by using a Microsoft® ActiveX® script.
You create ActiveX script code in the text box of the ActiveX Script Transformation Properties dialog box to determine which query will be executed. You display that dialog box by double-clicking the column mapping line for the transformation in the Transformations tab of the Data Driven Query Task Properties dialog box.
Note By default, the Data Driven Query task maps all source columns to all columns in the binding table as a single transformation. The mapping is displayed on the Transformations tab of the Data Driven Query Task Properties dialog box. It is recommended you do not use more then one ActiveX script for a Data Driven Query task, for that would reduce performance. For more information, see Enhancing Performance of DTS Packages.
- Edit the ActiveX script so that the return code matches DTSTransformStat_UpdateQuery.
The default return code for any query type is DTSTransformStat_InsertQuery. However, the query type labels are suggestions only. Although you can use the default Insert query to perform an Update query, for readability, you may want to change the return code to the appropriate one for an update.
The ActiveX script contains references to the DTSDestination columns collection, which refers to the binding table (Step 2). The parameters of the SQL operation will be applied to this table.
- Create the Update query.
Click Update from the Query type list on the Queries tab of the Data Driven Query Task Properties dialog box. Build the parameterized query, either by typing the query into the edit box or by using DTS Query Designer. Use a question mark (?) as the placeholder for the parameters that will be filled in by the processed data.
The attributes of a binding table column must match that of any parameter it is assigned to.
On the Queries tab, a grid displays one row for every parameter (?) entered in the selected query (Update). By default these rows will map to binding table columns ordinally, from the transformation previously defined. For more information, see Using Parameterized Queries in DTS.
To update or delete a destination row using multiple query types
- Define the source data and binding table as you did in the previous example (Steps 1 and 2).
- Provide the decision logic to apply to each query type.
Your script, based on the conditional logic you provide, returns one of four return codes. The return code tells DTS which query to execute for the current source row. You edit the default ActiveX script in the text box of the ActiveX Script Transformation Properties dialog box to include the logic and the return code.
The decision logic you use to determine the query type typically consists of branching statements (IF-THEN-ELSE or CASE).
In this example, the following two return codes are used:
DTSTransformStat_UpdateQuery
DTSTransformStat_DeleteQuery
For more information, see Using ActiveX Scripts in DTS and Using Return Codes in DTS.
- Create the queries.
For each query, select a query type from the Query type list on the Queries tab of the Data Driven Query Task Properties dialog box. Build the parameterized query, either by typing the query into the text edit box or by using DTS Query Designer.
The queries do not have to have the same number of parameters or map to the same columns in the binding table. The queries and the mappings are edited separately.