Incorporating Transactions in Packages

Packages use transactions to bind the database actions that tasks perform into atomic units, and by doing this maintain data integrity. Because all the database actions that are part of a transaction are committed or rolled back together, you can ensure that data remains in a consistent state. For example, a package with multiple Data Flow tasks, each updating and inserting data into a different database table, can use a transaction to guarantee that all the changes performed in the data flows are committed or rolled back. Distributed transactions carry this concept a step further, by letting you bind disparate operations on multiple operating systems into a single transaction.

You can use transactions in packages for the following purposes:

• Gather the results of several tasks into a single transaction to ensure consistent updates. For example, information about orders and line items that is stored in two different tables can be uploaded by two tasks that succeed or fail together.

• Ensure 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.

• Guarantee updates in an asynchronous environment. For example, a package might use 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.

• Carry out multiple transactions under the control of a single package. For example, using Execute Package tasks, a package can simultaneously run an end-of-day sequence of transactions on three different servers.

All Microsoft Integration Services container types—packages, the For Loop, Foreach Loop, and Sequence containers, and the task hosts that encapsulate each task—can be configured to use transactions. Integration Services provides three options for configuring transactions: NotSupported, Supported, and Required.

• Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.

• Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.

• NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

You configure transactions by setting the TransactionOption property on the container. You can set this property by using the Properties window in Business Intelligence Development Studio, or you can set the property programmatically. For more information, see Developer's Guide (Integration Services).