Data-driven Queries

The data-driven-query task is an advanced feature of DTS Designer, and is optimized for situations in which:

Incremental updates need to be applied to a data mart along with the capability of using transformations.
You need to use update or delete queries, or execute stored procedures. Although a data-driven query can include inserts, the Transform Data task is generally more efficient at inserting rows of transformed data into the destination table (depending on the capabilities of the destination provider).
Maximum flexibility in using queries is desired. Data-driven queries achieve this goal by employing parameterized queries. To use a data driven query, the destination data provider must support parameterized queries (the Transform Data task does not support parameterized queries).
Ability to handle complexity of the query and need for flexibility outweighs the need for performance (the Bulk Insert task is optimized for performance).

In data-driven query tasks, data is read from the source table and transformed, and a custom transformation returns an indicator of which up to four custom parameterized queries are executed on the destination table.

The DTS package and DTS Data Pump provide two methods of interaction at a data destination:

Insert-based
Data is read from the source, transformed, and then inserted or bulk copied into the destination using INSERT statements or the IRowsetFastLoad interface, if supported.
Data-driven-query-based
For every source row, a data-driven query executes a query at the destination. Data is read from the source and transformed, and a custom transformation returns an enumeration that indicates which one of four types of custom, parameterized queries are executed at the destination, using the transformed values in the destination row.

The two methods of interaction are mutually exclusive. If the query is insert-based, implement a DataPumpTask object. If the query is data-driven-query-based, implement a DataDrivenQueryTask object. A data-driven query can be used in a change-capture scenario, where the source query includes a column indicating whether the data in the source row was inserted, updated, or deleted. Another common change-capture scenario involves an attempt to insert a row, but to perform an update if the attempt fails. You can enhance the logic in data-driven query transformations using Lookup and GlobalVariable objects to assist the transformation.

The four types of queries that you can use in a data-driven query are:

Insert
Update
Delete
User-defined

These are enumerated in the DTSTransformStatus constant, which includes these return values:

DTSTransformStat_InsertQuery
DTSTransformStat_UpdateQuery
DTSTransformStat_DeleteQuery
DTSTransformStat_UserQuery

Note that although the names of the queries are preset, their content is not enforced. Any of the queries may be used for any desired operation. The nomenclature is provided as a convenient means of identification, based upon the primary purpose of the operation.

The DataDrivenQueryTask object appears and functions very similarly to the DataPumpTask object. The DataDrivenQueryTask object has:

No UseFastLoad or InsertCommitSize properties.
These are specific to high-speed bulk insert queries. Note that a SQLOLEDB destination connection initialized for the UseFastLoad property cannot be used for data-driven queries, as this connection only supports a subset of the normally-supported interfaces (specifically, the ICommand interface is not supported).
Query specifications.
There are options to specify insert, update, delete, or a user-defined queries. Each query has an associated Columns collection, containing specification of the destination columns (by name or ordinal) to be bound to parameters in the query. These columns must be populated in the order they appear in the query. Columns may be used more than once. The query is in the parameterized format supported by the destination provider. Parameters, and therefore columns, are optional in the query.

The DataDrivenQueryTask and DataPumpTask objects both function by iteratively transforming a source row into a destination row. When using a DataPumpTask object, destination row metadata is the schema of a destination table and the row’s values are inserted into that table. When using a DataDrivenQueryTask object, destination row metadata corresponds to all parameters for all queries specified. Values must be transformed into destination columns corresponding to parameters desired for the returned transformation status. For example, if column 1 is used only for an insert query and column 2 is used only for an update query, then column 1 must receive a value from the transformation if DTSTransformStat_InsertQuery is returned, but column 2 does not require a value.

Data-driven query operations are slower than IRowsetFastLoad because each operation is a single query, rather than part of a bulk insert. However, data-driven queries use the ICommand::Prepare interface if supported by the provider, and may realize performance gains over the IRowsetChange::Insert interface, which may use a simple INSERT statement without preparation.

To provide destination metadata to transformations, DTS package and DTS Data Pump allow two mutually-exclusive methods of specifying the metadata of parameters in the query.

Using Destination Metadata

As with the DataPumpTask object, a DestinationObjectName or DestinationSQLStatement property may be specified in the DataDrivenQueryTask object. An instance of a rowset is created to obtain its metadata, and is then released after the metadata is acquired.

The DestinationObjectName or DestinationSQLStatement properties do not have to have anything to do with the queries to be executed. They merely provide a means of describing the data types of the parameters of the queries. If an SQL statement is used, a destination column may be defined one or many times, and used one or many times in the query. For example, a query updating the value of a column based upon its prior value may define that column twice in the metadata, so it can hold two separate values in the query.

Using Task-Defined Metadata

The DataDrivenQueryTask object has a DestinationColumnsDefinitions collection which may be populated to describe the parameters of the queries. If this collection is populated, then the DestinationObjectName or DestinationSQLStatement properties are ignored.