Similar to SQL server, SQL Server Integration Services 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. 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 parent level (package level), and not supported at child level (Task, or Container). Transaction will not be applied at the child level.
- Supported: It does not start a new transaction, but it will join an existing transaction (if any).
- Required: If there an existing transaction then it will join otherwise, it will start a new transaction.
Transactions in SSIS Example
In this article we will 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
Rename the first task as the INSERTING DATA, and second SQL task as the UPDATING DATA
Now, Double click on the first Execute SQL Task will open the Editor to configure it. Let me select 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 to write the custom SQL command.
-- 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)
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 to write the custom SQL command.
-- SQL statement for SSIS Transaction example USE [SQL Tutorial] GO UPDATE [dbo].[SSIS Transactions Example] SET [Education] = 'Masters' WHERE [EmpID] = 1
Clicking OK to finish configuring Execute SQL Task. Let us run the package
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.
Configuring Transaction in SSIS
SQL server Integration Service allows us to apply transaction at Package Level (Master Level), Task Level, or Container Level. For now, we will apply transactions at package level .To do so, go to the Package properties, and look for the Transaction Option property.
By default, Supported is selected as the Transaction option. Let me leave it to default option only
Let me show you the Transaction option at Task level. For this, Please select the Execute SQL Task, go to its properties, and look for the Transaction Option property.
Let me change the Update statement in the second Execute SQL task to deliberately fail the task
-- SQL statement for SSIS Transaction example USE [SQL Tutorial] GO UPDATE [dbo].[SSIS Transactions Example] SET [EmpID] = 5 ,[Education] = 'Masters' WHERE [EmpID] = 1
Let me run the package
As you can that the package inserted four records, but the first record has not updated with Masters value.
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 Update Data task will be under the Required Transaction option.
Let me run the package
Now, let me show you the Table. As you can that the package hasn’t inserted single record, even the first task is successful.
Let me show you, what will happen if I change the Transaction Option of a First execute SQL Task from Supported to Not Supported?
It will insert the four records. Though, there is a Transaction required at package level (parent level), Not Supported option at child level will remove the transaction.
While working with the 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. This 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 then go to Microsoft download page and download the Microsoft Distributed Transaction Coordinator (MSDTC) Management Pack
Thank You for Visiting Our Blog.