SSIS Transactions with TSQL

Case
A couple of months ago I did a post on Transactions in SSIS, but that solution requires enabling the windows service Microsoft Distributed Transaction Coordinator (MS DTC). What if you can't (or prefer not to) use that service?

Solution
You can use the Transact SQL transactions to accomplish the same result. Same example as before. I want to empty and refill a table with values from a CSV file, but I want to keep the old data when the refill fails. My package:

Example

1) Container
Add a Sequence Container and drag the existing Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to it.

Sequence Container

2) Start Transaction
Add an Execute SQL Task before the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: BEGIN TRAN MyTran.

Start Transaction

BEGIN TRAN

3) Commit Transaction
Add an Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: COMMIT TRAN MyTran.

Commit Transaction

COMMIT TRAN

4) Rollback Transaction
Add an other Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: ROLLBACK TRAN MyTran.

Rollback Transaction

ROLLBACK TRAN

5) Precedence Contraint
Change the Value property of the Precedence Contraint between the Sequence Container and the Rollback from Success to Failure. If something fails in the Sequence Container the Rollback command will be executed.

Precedence Contraint

Failure

6) RetainSameConnection
Now the most important thing. Change the RetainSameConnection property of the database connection from false to True.

RetainSameConnection

7) The Result
That's all there is. Now you can test your package. You can open the CSV file in Excel to lock the file and fail the package.

The Result

* UPDATE *
Added the optional transactionname in case you want to re-execute. See comment Arthur Zubarev.

SSIS Transactions

Case
I want to empty and refill a table with values from a CSV file, but I want to keep the old data when the refill fails. My package:

Example

Solution
Fortunately SSIS supports transactions. Here is how you could implement it.

1) Container
All items that need the transaction need to be in one container. For example a Sequence Container, but the package itself is also an container. We will use the Sequence Container in this example. Drag the Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to a Sequence Container.

Sequence Container

2) TransactionOption
Change, in the properties of the Sequence Container, the TransactionOption field to Required.

TransactionOption

Transaction options documentation:

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

3) Testing
That's all there is. Now you can test your package. If the Data Flow fails the transaction will be aborted.

Failing Data Flow

Progress tab: Aborting transaction

Note: that SSIS's support for transactions relies on the windows service Microsoft Distributed Transaction Coordinator (MS DTC) which must be running on the same machine that is running the SSIS package. Otherwise you will get this error:

• The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Windows services can be managed by launching "Services" from the Windows Control Panel, Administrative Tools or typing "Services.msc" in the Run command on Start menu.

Note: If you use a truncate query instead of a delete query in the Execute SQL Task the package will hang. Apparently truncate locks the table. To solve this problem you must set ValidateExternalMetadata to False for all sources and destinations in the dataflows that use that same table.

ValidateExternalMetadata

* UPDATE *
An alternative for SSIS transactions are TSQL Transactions. See this article for more information.