Transactions in SSIS

Similar to the SQL Server, SQL Server Integration Services or SSIS also includes the Transaction Options to maintain the Data Integrity. There are three types of Transaction options in SSIS, and you can use them in containers, tasks, etc. 

The following are the list of available Transactions in SSIS

  • Not Supported: It does not start a new transaction, or it will not join an existing transaction (parent transaction). For example, if you specify the transaction as required at the parent level (package level), and not supported at the child level (Task, or Container). The SSIS transaction will not apply at the child level.
  • Supported: It does not start a new transaction, but it will join an existing transaction (if any).
  • Required: If they’re an existing transaction, then it will join. Otherwise, it will start a new transaction in SSIS Package.

Let us see the steps involved in configuring Transactions in SSIS with a practical example. Before we start explaining the SSIS transaction, let me create a Table in SQL Server

Transactions in SSIS 0

Transactions in SSIS Example

In this section, we show you the steps involved in configuring the Transactions in SSIS with example. For this, Drag two Execute SQL Tasks from SSIS Toolbar and drop it into the Control Flow region

Transactions in SSIS 1

Rename the first task as the INSERTING DATA, and second SQL task as the UPDATING DATA

Transactions in SSIS 2

Now, Double click on the first Execute SQL Task will open the Editor to configure it. Let me select the Connection as OLE DB Connection, which is connecting to the SQL Tutorial database. Next, we are using the Direct Input as the SQL statement so, click the … button to write the custom SQL command.

Transactions in SSIS 3

Please write the SQL statement here. As you can from the below screenshot, we are writing an INSERT Statement to insert four records into the table that we created earlier.

-- SQL statement for SSIS Transaction example
USE [SQL Tutorial]
GO
INSERT INTO [dbo].[SSIS Transactions Example] (
       [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales])
VALUES   ('Tutorial', 'Gateway', 'Education', 'Admin', 10000, 200)
	,('Imran', 'Khan', 'Degree', 'Skilled Professional', 15900, 100)
	,('Doe', 'Lara', 'Masters','Management', 15000, 60)
	,('Ramesh', 'Kumar', 'High School', 'Professional', 65000, 630)
Transactions in SSIS 4

Next, Double click on the second Execute SQL Task. Here also we are selecting the Connection Type as OLE DB Connection, which is connecting to SQL Tutorial database. Next, we are using the Direct Input as the SQL statement. So, click the … button.

Transactions in SSIS 5

Write the custom SQL statement here. As you can see from the following screenshot, we are writing an UPDATE statement to update the Education to Masters for Employee whose ID is 1.

-- SQL statement for SSIS Transaction example
USE [SQL Tutorial]
GO
UPDATE [dbo].[SSIS Transactions Example]
   SET [Education] = 'Masters'
WHERE [EmpID] = 1
Transactions in SSIS 6

Clicking OK to finish configuring Execute SQL Task. Let us run the SSIS Transactions package

Transactions in SSIS 7

Let us open the SQL Server Management Studio Query window to Preview the data. As you can that the package inserted four records, and updated the first record with Masters.

Transactions in SSIS 8

Configuring Transaction in SSIS

SQL Server Integration Service allows us to apply transactions at Package Level (Master Level), Task Level, or Container Level. For now, we will use SSIS transactions at the package level. To do so, go to the Package properties and look for the Transaction Option property.

By default, Supported selected as the Transaction option. Let me leave it to default option only

Transactions in SSIS 9

Let me show you the SSIS Transaction option at the Task level. For this, Please select the Execute SQL Task, go to its properties, and look for the Transaction Option property.

Transactions in SSIS 10

Let me change the Update statement in the second Execute SQL task to fail the task deliberately

-- SQL statement for SSIS Transaction example
USE [SQL Tutorial]
GO
UPDATE [dbo].[SSIS Transactions Example]
   SET   [EmpID] = 5 
        ,[Education] = 'Masters'
WHERE [EmpID] = 1
Transactions in SSIS 11

Let me run the SSIS Transactions package

Transactions in SSIS 12

As you can that the package inserted four records, but the first record has not updated with Masters’s value.

Transactions in SSIS 13

What if we want to rollback the complete operation when the task fails?. I mean How to remove the inserted rows from the table if the Update statement fails?.

For this, we have to change the Transaction Option from Supported to Required at the package level. It means both the Insert Data task and Updates Data task will be under the Required Transaction option.

Transactions in SSIS 14

Let me run the SSIS Transactions package

Transactions in SSIS 16

Now, let me show you the Table. As you can that the package hasn’t inserted a single record. Even the first task is successful.

Transactions in SSIS 17

Let me show you what will happen if I change the SSIS Transaction Option of a First execute SQL Task from Supported to Not Supported?

Transactions in SSIS 18

It will insert the four records. Though there is a Transaction required at the package level (parent level), the Not Supported option at the child level will remove the transaction.

Transactions in SSIS 19

While working with the SSIS transactions, some people might face the following error

Error: 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. It could occur because the MSDTC Service is not running.

If this is the case, then go to the services and start the Distributed Transaction Coordinator service. If you didn’t find the service, go to Microsoft download page and download the Microsoft Distributed Transaction Coordinator (MSDTC) Management Pack

Transactions in SSIS 15

Comments are closed.