Data Driven Query Task
The Data Driven Query task allows you to perform flexible, Transact-SQL based operations on data, including stored procedures and INSERT, UPDATE or DELETE statements. For each row in a source rowset, the Data Driven Query task selects, customizes, and executes one of several SQL statements. You select which statement to execute via a constant return value set in a Microsoft® ActiveX® script transformation. Based on the return constant you use in the script, one of four different parameterized SQL statements that you create may be executed for each source row.
When designing a Data Driven Query task, you need to decide whether the task should include a single query or multiple queries. For example, if you only want the task to delete data, you use a single query (a Delete query). If you want to update some rows and delete others, you need to use two queries (an Update and Delete query). If you use multiple queries, you need to provide scripting code that supplies conditional logic. That logic determines when each query type is applied to the data.
Using the Data Driven Query task, you can:
- Run any large collection of updates that are not necessarily inserts. For example, you can purge expired historical data from a database, given a list of keys generated by another database.
- Perform traditional file maintenance. For example, you can optionally insert or update records depending on their previous existence or some other external factor.
- Customize Microsoft SQL Server™ transformable subscriptions, which are used to distribute and transform incremental changes during replication. For example, you can tailor the response of each subscriber to insert, delete, or update requests.
Use the Data Driven Query task when, for each of many source rows, you must either:
- Choose among more than one edit operation.
-or-
- Perform a non-insert edit (for example, perform an update or delete operation, or execute a stored procedure).
The Transform Data task and the Bulk Insert task are optimized for insert operations. Choose the Data Driven Query task for insert operations only if these tasks do not meet the requirements of your application.
You can use either DTS Designer or the DTS object model to create and manage Data Driven Query tasks. For more information about programming for the Data Driven Query task, see DataDrivenQueryTask2 Object and Creating a Transformable Subscription Using Visual Basic.