Incorporating Transactions in a DTS Package

You use database transactions to bind multiple updates into a single atomic unit. In this way, you help to ensure that your data remains in a consistent state. Distributed transactions carry this concept a step further, allowing you to bind disparate operations on multiple platforms into a single transaction.

Data Transformation Services (DTS) uses functions offered by the Microsoft® Distributed Transaction Coordinator (MS DTC) to extend the benefits of distributed transactions to the DTS package developer. For transactions to work, MS DTC must be running on the computer executing the package. Use the SQL Server Service Manager to start MS DTC or to verify that it is running.

With DTS transactions, you can:

• Gather the results of several tasks into a single transaction and so ensure consistent updates. For example, orders and line items can be uploaded by two tasks, which succeed or fail together.
  
  
• Perform consistent updates on multiple database servers. For example, a customer address can be changed in two different online transaction processing (OLTP) systems, all in the context of one transaction.
  
  
• Combine database modifications and message queue operations in a single transaction to provide guaranteed updates in an asynchronous environment. For example, a package might employ a Message Queue task to read and delete a message bearing the name of a file to upload. If the task that uploads the file fails, the subsequent rollback both reverses the database changes and puts the message back on the queue. The package can be restarted with no user intervention.
  
  
• Carry out multiple transactions under the control of a single package. For example, using Execute Package tasks, you can simultaneously run an end-of-day sequence of transactions on each of three different servers. For more information, see Execute Package Task.

You can use DTS Designer to manage package transactions, or you can access the same functionality programmatically. For more information, see DTS Designer and DTS Programming Reference.