Home > SSIS > Manage Transactions in SSIS

Manage Transactions in SSIS


Transaction is used to maintain data integrity. We have options to create a transaction and manage in Database management system. In the same manner , we can manage transactions in SSIS by using TransactionOption property.

This property is available in packages, For Loop, Foreach Loop, and Sequence containers, and the tasks. These components can be used as a single unit task for the transaction.

The meaning of transaction is about commit or rollback the data in the transaction of SSIS package.

Distributed transactions carry this concept a step further. So, we should start DTC (i.e. MSDTC) Service before package execution.

TransactionOption values:

Required – Indicates that the container starts a transaction, unless one is already started by its parent container.

Supported – Indicates that the container does not start a transaction, but joins any transaction started by its parent container.

NotSupported – Indicates that the container does not start a transaction or join an existing transaction.

Implementation

Summary: In this example, I will be using a table and two execute SQL  task and a sequence container.

Create a table named Nation with a column nation_name varchar(10) and Add few rows in it.

Step 1: Create a package and add a sequence container and include two execute SQL  tasks in it.

Step 2: Set TransactionOption = Required for sequence container and Set TransactionOption = Supported for two Execute SQL Tasks component.

Step 3: Configure Execute SQL Task

Step 4: Configure Execute SQL task 1

Step 5: Set property ForceExecutionResult = Failure of Execute SQL Task 1 to make manual failure of the package and Execute package now.

Step 6: Now, check the Nation table. Even-though execute SQL Task executed successfully, it will be rollback due to the transaction failure. Because, Sequence container is a transaction and Execute SQL tasks are child transaction.

Step 7: Now,  Set property ForceExecutionResult = None of Execute SQL Task 1 and execute package.

At last, entire table will be updated with two statements ‘USA’ and ‘US’. So, the last update with ‘US’ will be committed to nation table.

Thanks for reading.

Advertisements
  1. January 12, 2015 at 1:02 pm

    It’s going to be ending of mine day, however
    before end I am reading this impressive post to improve my knowledge.

  1. No trackbacks yet.

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

%d bloggers like this: