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.

 

Playing with SSIS is fun

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s