SSIS Transaction

Introduction

Continuing my journey with MS SSIS, now it's time to discuss about SSIS transaction. We have to understand the needs of Transaction and how we use the transaction in SSIS. In this article I am trying to discuss about it.

What the Transaction is

Before going to the SSIS Transaction we have to know some facts related to transaction.

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. The properties of the transaction are mentioned bellow.

Atomicity: 

Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

Consistency: 

Ensures that the database properly changes states upon a successfully committed transaction.

Isolation: 

Enables transactions to operate independently of and transparent to each other.

Durability: 

Ensures that the result or effect of a committed transaction persists in case of a system failure.

Type of SSIS Transaction

There are two type of transaction that SSIS supports.

Distributed Transaction

Distributed Transaction uses two phase commit transaction with Distributed Transaction Coordinator (DTC) service. We must sure that this service runs before we run our package that uses the service. We don't have to explicitly state BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN because they are handled automatically by DTC services.

Native SQL Server engine transaction

This transaction uses explicit BEGIN TRAN/COMMIT/ROLLBACK TRAN, explicitly in Execute SQL Task component. We must setting up the involved connection manager's property RetainSameConnection from false to true.

Where we can set the Transaction in SSIS

Transaction support is built in to SSIS.  The property that we have to set for SSIS transaction is TransactionOption property. The TransactionOption property exists at the package level, container level such as Sequence, For Loop, Foreach Loop etc and also at any Control Flow task such as Execute SQL task, Data Flow task, etc. 

Caution about running MSDTC

The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. If we execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, we will get an error message like the following:

Error: 0xC001401A at Transaction: 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.

Options of TransactionOption properties

There are 3 options for TransactionOption properties are mentioned bellow.

NotSupported: The component will not join to transaction scope.
Supported: The component will join parent's transaction scope if exists
Required: The component will join parent's transaction scope if exists, otherwise it will start new component.

Understand the Checkpoints

When we are working with a complicated and long running SSIS package and we want the ability to restart the package if it fails and have it starts at the point of failure. In others words if we don't want to execute the tasks that are already successful if the package restarted. This is done by establishing check points.

To understand it, let's assume that we have 10 tasks in the package. When the package is executed The Tasks -1 to Tasks -5 runs success fully at Tasks-6 it gives an error. When the package restarted for establishing check points it starts from Tasks -6 not from Tasks-1.

To enabling restart ability within a package required first enabling a package to use check points, and second settings the specific tasks and containers to write checkpoints.

Example of Establishing Transactions

 Here is a simple example to understand the steps behind establishing the Transaction.

Step-1 [ Start the Microsoft Distributed Transaction Coordinator (MSDTC) ]

Control Panel à Administrative Tools à Services Console

Step-2 [ Setting TransactionOption Property to Required ]

Example of Establishing Checkpoints

Step-1 [ Open the Package property window and open Control Flow Tabs ]

Step-2 [ Set the SaveCheckpoints property at the package level to True ]

This property allows Checkpoints to be saved during package execution.

Step-3 [ Set the CheckpointFileName property ]

Here we provide a valid path and fine name of the checkpoint files. The package uses this file to maintain their state information. When the package execution fails, it reads the file to determine the last successful task and start the package execution after that task.

Step-4 [ Set the CheckpointUsage property ]

If this property is set IfExist, which causes the package to run from beginning if the Checkpoint file is not present or to run from the identified point if the file exists.

  

Hope you like it.

Posted by: MR. JOYDEEP DAS