SSIS – Understanding Transaction Scope

etlssistransaction

My question is in regards to the scope of a transaction when using SSIS transactions. If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?

Best Answer

From BOL

If a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.

If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?

SSIS will commit the task with the sequence container.

IMHO, best is to use TSQL Transactions instead of SSIS Transactions.

Also refer to : Design Pattern: Avoiding Transactions in SSIS

Related Question