Transactions in SSIS

Similar to the 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

Create a Table in Database

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

Execute SQL Task in Control Flow

Rename the first task as the INSERTING DATA, and second 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 database. Next, we are using the Direct Input as the statement so, click the … button to write the custom command.

Execute SQL Task Editor

Please write the 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.

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)
Insert Statement Query

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 database. Next, we are using the Direct Input as the statement. So, click the … button.

Transactions Query 5

Write the custom 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.

UPDATE [dbo].[SSIS Transactions Example]
   SET [Education] = 'Masters'
WHERE [EmpID] = 1
Update Query

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

Transactions in SSIS 7

Let us open the 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 Table Output

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

Set Transaction Option Supported

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

UPDATE [dbo].[SSIS Transactions Example]
   SET   [EmpID] = 5 
        ,[Education] = 'Masters'
WHERE [EmpID] = 1
Update Statement

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 Destination Table

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 Table Empty

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 Output

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

Start Distributed Transaction Coordinator service