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.