Data Driven Query Task

A data driven query is a powerful tool that you can use to update a data warehouse.

Data driven queries allow you to scan rows in your source data, and based on the values it finds in a row, automatically select a particular query type to execute.

For example, you want to scan an employee table and perform these operations:

• Update rows where the employee salary is over $40,000.
• Delete rows where the employee title is Political Consultant.
• Insert rows for new employees.

By configuring a single data driven query in a package, you can perform all those tasks.

When to Use Data Driven Queries

An advanced feature of the DTS Designer, the Data Driven Query task is optimized for situations in which:

• Maximum flexibility in using queries is desired. Data-driven queries achieve this goal by using parameters. To use a data driven query, the destination data provider must support parameterized queries (ICommand interface).
• Ability to handle complexity of the query and need for flexibility outweighs the need for performance (the Bulk Insert task and Transform Data task with fast loading turned on is optimized for performance).
• 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.

Query Types Used

Data driven queries can be one of four types; however, it is possible to use multiple query types within a single Data Driven Query task.

• Insert. A data driven insert query may look like:

  INSERT INTO Categories(CategoryID, CategoryName, Description, Picture)
        VALUES (?,?,?,?)

    

• Update. A data driven update query may look like:

  UPDATE Categories SET Description = 'Mustard' WHERE CategoryID = (?) AND
        CategoryName = 'Produce'

    

• Delete. A data driven delete query may look like:

  DELETE FROM Categories WHERE CategoryName = 'Produce'
        AND Description = (?)

    

• User. These are stored procedures that take parameters; for example:

  sp_mystoredproc ?,?