Transactions in SSIS

Transactions in SSIS can be implemented at any level like package level, task level, Container level etc.

SSIS transactions support 3 values:

Supported: An SSIS object can only join the transaction, cannot initiate the transaction
Not Supported: An SSIS object neither initiate the transaction nor joins the transaction
Required: An SSIS object can initiate the transaction

Now we know the meaning of transaction values, let’s go a step further  and understand transaction’s behavior in case of Parent – child relationship:

Scenario 1: Transaction property set to ‘Required’ at parent level
Suppose at package level, I set the transaction property to ‘Required’. This means that package initiate the transaction. Now all its child whether sequence container or task would join this transaction. If any task fails, everything would be roll back.

Scenario 2: Transaction property set to ‘Supported’ at parent level
If the transaction property is set to ‘Supported’, and if any task fails, then only failed task is rolled back, rest of the tasks would be committed

Scenario 3: Transaction property not configured at parent level but is set to ‘Required’ at child level
In this scenario, the child will initiate the transaction