Enhancing Data Driven Queries in DTS

By changing properties in Data Transformation Services (DTS) Designer or via the DTS object model, you can configure the Data Driven Query task to:

• Fail on the first error, or continue, perhaps logging errors, until a maximum error count is reached.
  
  
• Commit modifications immediately, or join the package transaction to make all edits succeed or fail together.
  
  
• Use lookup queries to help populate destination columns or determine which query to execute.

Error Handling and the Data Driven Query Task

The Data Driven Query task offers a number of features to support error handling and restarts:

• To adjust the response of the Data Driven Query task to failures, you can either change the Maximum errors property in the Data Driven Queries dialog box or adjust the MaximumErrorCount property in the DTS object model.

  When the number of failures exceeds this maximum value, the task halts and the step fails.

• You can log errors to a file you name, with formatting you provide.
  
  
• In order to support restarts, you can configure the Data Driven Query task to operate only on a numbered subrange of the source rowset.

Transactions and the Data Driven Query Task

The Data Driven Query task can join the package transaction. Before using the Data Driven Query task in transactions, consider the following:

• If the Data Driven Query task does not join the package transaction, updates are made one at a time, as they are requested.
  
  
• If the Data Driven Query task does join the package transaction, successfully run queries remain in the transaction at the conclusion of the task. These results are subject to commit or rollback in the current or following steps, or at package completion.
  • In order to join the package transaction, the binding table connection must support transactions.
    
    
  • Data Driven Query task failure does not automatically roll back successfully run queries; task success does not automatically commit them. Commit and rollback happen only in response to user package and workflow settings. For more information, see Configuring Properties for DTS Transactions.

Lookup Queries and the Data Driven Query Task

The Data Driven Query task can include lookup queries, which are additional parameterized queries that can be used to look up or modify data on local or distant connections.

Before adding lookup queries to the Data Driven Query task, consider the following:

• If the Data Driven Query task joins the package transaction, and the lookup connection supports transactions, any updates made by lookup queries also take part in the package transaction. For more information, see Lookup Queries.
  
  
• If the Data Driven Query task does not join the package transaction, or Maximum errors is not equal to zero, the possibility exists that a lookup might successfully execute before the corresponding data driven query fails. If the lookup modifies data, inconsistent updates might result.

Other Usage Considerations

Before using the Data Driven Query task, you also should consider the following:

• The data driven query task binding table must be able to support the OLE DB ICommand interface. Due to this restriction, binding tables such as text files are not supported.
  
  
• You should use unique connections for the source, binding table, and any lookup queries.

See Also

Data Driven Query Task