Similar to the 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.
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.
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
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 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.
-- 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.
-- 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 SSIS Transactions 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 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
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.
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
Let me run the SSIS Transactions package
As you can that the package inserted four records, but the first record has not updated with Masters’s 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 Updates Data task will be under the Required Transaction option.
Let me run the SSIS Transactions package
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.
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?
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.
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