Transaction support is built in to SSIS. The TransactionOption property exists at the package level, container level (e.g. For Loop, Foreach Loop, Sequence, etc.), as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.). TransactionOption can be set to one of the following:
Transaction options:
NotSupported - Specifies that no transaction will be started for this container, and consequently, the outcome of the current transaction, if one has been started by a parent container, will not affect the data that may be altered during execution of this container. This means that changes will not roll back, even if the parent container started a transaction.
Supported - Specifies that this container will not start a transaction. However, it will participate in a transaction if the parent container started one. This is the default.
Required - Specifies that this container will cause a new transaction to be started unless the parent container already has a transaction, in which case, the parent’s transaction will be joined.
To configure a package to use a single transaction:
1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want to configure to use a transaction.
2. In Solution Explorer, double-click the package to open it.
3. Click the Control Flow tab.
4. Right-click anywhere in the background of the control flow design surface, and then click Properties.
5. In the Properties window, set the TransactionOption property to Required.
6. 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.
7. In the Properties window, set the TransactionOption property to Supported.
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information.
8. Repeat steps 6 and 7 for each task and container that you want to enroll in the transaction.
The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction. If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message.
Tags:
Transaction options:
NotSupported - Specifies that no transaction will be started for this container, and consequently, the outcome of the current transaction, if one has been started by a parent container, will not affect the data that may be altered during execution of this container. This means that changes will not roll back, even if the parent container started a transaction.
Supported - Specifies that this container will not start a transaction. However, it will participate in a transaction if the parent container started one. This is the default.
Required - Specifies that this container will cause a new transaction to be started unless the parent container already has a transaction, in which case, the parent’s transaction will be joined.
To configure a package to use a single transaction:
1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want to configure to use a transaction.
2. In Solution Explorer, double-click the package to open it.
3. Click the Control Flow tab.
4. Right-click anywhere in the background of the control flow design surface, and then click Properties.
5. In the Properties window, set the TransactionOption property to Required.
6. 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.
7. In the Properties window, set the TransactionOption property to Supported.
To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information.
8. Repeat steps 6 and 7 for each task and container that you want to enroll in the transaction.
The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction. If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message.