How to: Configure a Package to Use Transactions
When you configure a package to use transactions, you have two options:
Have a single transaction for the package. In this case, it is the package itself that initiates this transaction, whereas individual tasks and containers in the package participate in this single transaction.
Have multiple transactions in the package. In this case, the package supports transactions, but individual tasks and containers in the package actually initiate the transactions.
The following procedures describe how to configure both options.
Note
The TransactionOption property described in the following procedures influences whether or not the value of the IsolationLevel property requested by a container is applied. For more information, see the description of the IsolationLevel property in the topic, Setting Package Properties.
Configuring a Single Transaction
In this option, the package itself initiates a single transaction. You configure the package to initiate this transaction by setting the TransactionOption property of the package to Required.
Next, you enlist specific tasks and containers in this single transaction. To enlist a task or container in a transaction, you set the TransactionOption property of that task or container to Supported.
To configure a package to use a single transaction
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want to configure to use a transaction.
In Solution Explorer, double-click the package to open it.
Click the Control Flow tab.
Right-click anywhere in the background of the control flow design surface, and then click Properties.
In the Properties window, set the TransactionOption property to Required.
On the design surface of the ControlFlow tab, right-click the task or the container that you want to enroll in the transaction, and then click Properties.
In the Properties window, set the TransactionOption property to Supported.
Note
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information, see Integration Services Connections.
Repeat steps 6 and 7 for each task and container that you want to enroll in the transaction.
Configuring Multiple Transactions
In this option, the package itself supports transactions but does not start a transaction. You configure the package to support transactions by setting the TransactionOption property of the package to Supported.
Next, you configure the desired tasks and containers inside the package to initiate or participate in transactions. To configure a task or container to initiate a transaction, you set the TransactionOption property of that task or container to Required.
To configure a package to use multiple transactions
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want to configure to use transaction.s
In Solution Explorer, double-click the package to open it.
Click the Control Flow tab.
Right-click anywhere in the background of the control flow design surface, and then click Properties.
In the Properties window, set the TransactionOption property to Supported.
Note
The package supports transactions, but the transactions are started by task or containers in the package.
On the design surface of the ControlFlow tab, right-click the task or the container in the package for which you want to start a transaction, and then click Properties.
In the Properties window, set the TransactionOption property to Required.
If a transaction is started by a container, right-click the task or the container that you want to enroll in the transaction, and then click Properties.
In the Properties window, set the TransactionOption property to Supported.
Note
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information, see Integration Services Connections.
Repeat steps 6 through 9 for each task and container that starts a transaction.
See Also