Transaction in SSIS - Required and Supported Options...

Transaction in SSIS - Required and Supported Options... 

Transaction is a set of tasks present in single execution unit. Each transaction begins with a specific task and ends only when all the tasks in the group are successfully completed. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure
 

In case of SQL Server Integration Service, there are 3 types of Transaction, they are Required, Supported and Not Required. The blog explains the transaction Isolation levels as Serialization. SSIS transactions are based on the Microsoft Distributed Transaction Coordinator (MSDTC). They are enabled at the Task/Container level by setting the TransactionOption property to Required. For a transaction to work at runtime, MSDTC must be enabled or configured on all machines involved in the transaction.

 

Step 1: Open the BIDS. Drag and drop the Sequence Container into the package as shown.

Step 2: Set the Transaction Option properties of Sequence Container to Required from Supported. Add 3 or 4 Execute SQL task as shown below.

Step 3: Check the Transaction Option properties of all the 3 Execute SQL task are set to Supported. Create 3 table as Customer, Employee and Vehicle. Write the Insert SQL Queries in respective SQL task. The SQL Queries are provided below

Create table Customer

(

            CustomerID int,

            CustomerName Varchar(100),

            CustomerAddress Varchar(100)

)

 

Create table Employee

(

            EmployeeID int,

            EmployeeName Varchar(100),

            EmployeeAddress Varchar(100)

)

 

Create table Vehical

(

            VehicalID int,

            VehicalNumber  Int Not NULL,

            VehicalName Varchar(100)

)

 

Once the tables are created, add the insert queries into SSIS Execute SQL task.

Insert Customer Values (1, 'Daniel', 'Chennai')
Insert Employee Values (1, 'Albert', 'Chennai')
Insert Vehical Values (1, NULL, 'MIG - 5')

 

Execute the process now. The First and Second Execute SQL task should run successfully and the 3rd will fail to revert back all the earlier insert done in the Database.

the error is not due to the Insert command, its due to the Distributed Transaction Coordinator - DTC or MSDTC which needs to be started. DTC is the process which takes care of Transaction process.

 

On restart of the DTC Process, Error occurred is due the Insert Command, where the Vehical table has Vehical

Number column which is set to NOT NULL. Lets Check the tables Customer and Employee because they ran successfully. According to the transaction, there should be a rollback in the Database.

So no records are inserted into the Customer and Employee table. its due to the Rollback happened properly. lets correct the Data and re-run the process again.

Now the Package executed properly, let's check the data in Database

So the transaction are executed properly and data got committed in the respective tables.

 

Note:

Disadvantages of DTC

1. Configuring and Enabling the DTC is required in the respective Server.

2. Server must support DTC which is not possible when the Database is using Non Windows Server

3. As DTC needs to coordinate between the Servers, its performance will be Higher

4. Some of the Task doesn't support the transaction such as XML and File System

 

Is there any alternative way, not to use TRANSACTION - Yes. Here we Go.....

Step A: Truncate the Customer, Employee & Vehical table once again to learn the New way of Transaction. Drag and drop the 3 Execute SQL task as shown below.

Step B: As the Name Suggested in the Execute SQL task add the respective SQL Queries as "Begin Transaction Daniel", "Commit Transaction Daniel", "Rollback Transaction Daniel"

Step C: Change the Precedence Constraint path of Rollback Execute SQL task to Failure as shown above and Commit to Success. Select the Connection String from the Connection Manager and Set the Property - Retain Same Connection to True.

 

Step D: Run the Package. it should throw the Error, if the Vehical Insert Query is set to NULL values and should not insert the data in all the 3 tables.

Step E: Package got failure and move to Rollback Transaction, as we expected. but we need to Check the Database from the User Point of View.

Step F: Change the NULL Value to Integer Value and Run the Package again. Now the Package should run fine and provide the data in the Database tables as expected.

other Transaction option will be explained in the later session of blog.

 

keep willing and keep blogging :-)

Thank you very much for waiting long time. I know its valuable waiting...

Regards

Daniel J

Dataware and Data Mining Architect...