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.
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 SQL Server Data Tools (SSDT), 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 Control Flow 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.
[!UWAGA]
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information, see Integration Services (SSIS) 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 SQL Server Data Tools (SSDT), 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.
[!UWAGA]
The package supports transactions, but the transactions are started by task or containers in the package.
On the design surface of the Control Flow 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.
[!UWAGA]
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information, see Integration Services (SSIS) Connections.
Repeat steps 6 through 9 for each task and container that starts a transaction.