How to use Transactions in SSIS (SQL Server Integration Services)

Reading Time: 8 minutes

Introduction

As you know, transactions are units of work which performed against a database. SQL Server makes uses of this concept for Consistency and Durability of data in a system. Due to Atomicity feature, transaction or all the modifications inside a transaction will be committed or they will all rollback.
Like SQL Server, SQL Server Integration Services (SSIS) utilized transactions concept to maintain data integrity. So, packages also use transactions to atomize the commands enacted by the tasks.
All types of SSIS containers can create and participate in transactions. Among these containers, Package is at the highest level and Control Flow Tasks are at the lowest level. Other containers include For Loop, Foreach Loop and Sequence containers.

Prerequisites

Working with Transactions in SSIS has its own challenges. Since SSIS transaction support relies on the Microsoft Distributed Transaction Coordinator (MSDTC) service, whether your transaction is distributed or not, the following should be observed before using SSIS transactions.

• All systems involved must support DTC transactions
• DTC service must be running on all sides

Otherwise, you will encounter the following error while running the package:
“Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B The Transaction Manager is not available. The DTS transaction filed to start. This could occur because the MSDTC Service is not running”

Types of Transactions in SSIS

Integration services have three options to adjust the transaction which are adjusted by TransactionOption property of the selected container (or selected package).

• Required: When this option is used, the container will create a new transaction unless the parent container has already created a transaction, in which case this container will only join the same transaction and will not create a new transaction itself.
• Supported: When this option is used, the container will not create a new transaction, but if the parent container has created a transaction, it will join it.
• Not Supported: In this case, not only does the container not make a transaction, but it will not participate in the parent container transaction (if any). In such cases, the error in the transaction will not cause this container to rollback.

We will now explain the above with a practical example:
Suppose that you have a very large table to store customer orders that contain sales information for several years and you want to archive the old information in this table. For this purpose, we want to design a package and examine the impact of different types of TransactionOption on it.
In order to design this package, we have created SalesOrder tables for sales information and SalesOrder_Archive table with the same structure for archiving old information. Also, we created two additional tables called ArchiveConfig, use to store the last archived data and PackageExecutionLog, use to store the package execution log.

Designing the Package

1. First, we create the mentioned tables

   --Create sample database
   CREATE DATABASE [SSIS_Transactions]
   GO
   USE [SSIS_Transactions]
   GO
   
   --Create operatonal table
   CREATE TABLE [dbo].[SalesOrder]
   (
   	SaleOrderId INT IDENTITY(1,1) PRIMARY KEY,
   	CustomerId INT NOT NULL,
   	SalesPersonId INT NOT NULL,
   	OrderDate DATE NOT NULL,
   	TaxAmt MONEY NOT NULL,
   	TotalDue MONEY NOT NULL
   )
   CREATE NONCLUSTERED INDEX NCIX_OrderDate ON [dbo].[SalesOrder] ([OrderDate],[CustomerId])
   GO
   
   --Create archiving table with same structure as operational table, except IDENTITY option
   CREATE TABLE [dbo].[SalesOrder_Archive]
   (
   	SaleOrderId INT PRIMARY KEY,
   	CustomerId INT NOT NULL,
   	SalesPersonId INT NOT NULL,
   	OrderDate DATE NOT NULL,
   	TaxAmt MONEY NOT NULL,
   	TotalDue MONEY NOT NULL
   )
   GO
   
   --Create config table to store last archiving date of each database table(s)
   CREATE TABLE [dbo].[ArchiveConfig]
   (
   	Id INT IDENTITY(1,1) PRIMARY KEY,
   	TableName VARCHAR(100),
   	LastArchiveOrderDate DATE
   )
   GO
   
   --Create log table for storing history of archiving package execution
   CREATE TABLE [dbo].[PackageExecutionLog]
   (
   	Id INT IDENTITY(1,1) PRIMARY KEY,
   	PackageName VARCHAR(100) NOT NULL,
   	ExecutionTime DATETIME NOT NULL
   )
   GO

    

2. Then, we use an Execute SQL Task named “Get Last Archived Date From Config Table“, to get the desired date for the archive and save the returned value of this control in a variable called ArchiveDate.

   DECLARE @LastArchiveDate DATETIME = '2018-01-01'
   SELECT @LastArchiveDate = [LastArchiveOrderDate] FROM [dbo].[ArchiveConfig] WHERE [TableName] = 'SalesOrder'
   SELECT DATEADD(DAY,1,@LastArchiveDate) AS LastArchiveDate

3. Then, a Data Flow Task control named “Insert Data Into Archive Table” is used to read the sale data from the SalesOrder table and transfer it to the destination archive table named SalesOrder_Archive. The following code is used to obtain information.

   SELECT
     [SaleOrderId],
     [CustomerId],
     [SalesPersonId],
     [OrderDate],
     [TaxAmt],
     [TotalDue]
   FROM
     [dbo].[SalesOrder]
   WHERE
     [OrderDate] = ?

4. In the next step, using an Execute SQL Task named “Delete Archived Data From Source Table”, we will remove the archived information from the source table.

   DELETE FROM [dbo].[SalesOrder] WHERE [OrderDate] = ?

5. Then, we update the value of the LastArchivedOrderDate field in the ArchiveConfig table using the ArchiveDate variable. To do this, we use an Execute SQL Task control called “Update Config Table”.

   UPDATE [dbo].[ArchiveConfig] SET [LastArchiveOrderDate] = ? WHERE [TableName]='SalesOrder'

6. Finally, for each package execution, we enter a record inside the PackageExecutionLog table. To do this, we use another Execute SQL Task control called “Log Package Execution”.

   INSERT INTO [dbo].[PackageExecutionLog] ([PackageName], [ExecutionTime]) VALUES ('SalesOrder', GETDATE())

The final package is as follows:

Adjustment of Different Transactions in the SSIS Package

1st Scenario: Adjusting Single Transaction for each Package:

In this example, the package itself will start the transaction and its TransactionOption feature will be set to required value.

We also set the TransactionOption of other tasks to supported value.

Then, to understand the effect of the error on the execution of the package, we change the Insert command inside the Log Package Execution. This command will cause an error due to the Null value of the ExecutionTime field.

Running the following command indicates that three records must be transferred to the SalesOrder_Archive table by running the package.

SELECT * FROM [dbo].[SalesOrder] WHERE [OrderDate] = '2018-01-02'

Now, if the package is executed, it will encounter an error, and because the Log Package Execution Task is part of the transaction, all operations will be rolled back.

Running the following command again will confirm this.

SELECT * FROM [dbo].[SalesOrder] WHERE [OrderDate] = '2018-01-02'

2nd scenario: Adjusting the Single Transaction for each Container

In this example, we’re going to put the Tasks that perform the main archiving operation and their failure should cancel the operation into a Sequence Container and enable the transaction for this container.

To this end, we set the TransactionOption properties as follows.

• Package ——> Supported
• Sequence Container ——> Required
• Get Last Archived Date From Config Table ——> Supported
• Log Package Execution —–> Supported

Although we will encounter an error when running the package, the main transaction will be successful and the error will not affect the main operation.

Running the following commands will confirm this.

SELECT * FROM [dbo].[SalesOrder] WHERE [OrderDate] = '2018-01-02'
SELECT * FROM [dbo].[SalesOrder_Archive] WHERE [OrderDate] = '2018-01-02'

However, if we change the command in the “Update Config Table” Task and update the value of the ID field, this task will encounter an error.

This error will cause the transaction to rollback.

SELECT * FROM [dbo].[SalesOrder] WHERE [OrderDate] = '2018-01-02'
SELECT * FROM [dbo].[SalesOrder_Archive] WHERE [OrderDate] = '2018-01-02'

3rd scenario: NotSupported transaction

Now to check the effect of transaction on NotSupported Containers or Tasks, we will add an Execute SQL Task to the Sequence Container in the previous step package called “Create Temporary Table Temp_SalesOrder” just to create a temporary table and set its TransactionOption feature to NotSupported.

Then, to make sure that this table does not already exist, we first delete the command and then add its creation.

DROP TABLE IF EXISTS Temp_SalesOrder
GO
CREATE TABLE Temp_SalesOrder
(
  SaleOrderId INT NOT NULL,
  CustomerId INT NOT NULL,
  SalesPersonId INT NOT NULL,
  OrderDate DATE NOT NULL,
  TaxAmt MONEY NOT NULL,
  TotalDue MONEY NOT NULL
)
GO

As a reminder, “Update Config Table” Task will cause an error by updating the ID field.
Now we will run the package.

As you can see, the execution of the package will encounter an error and the transaction will be rolled back. Running the following command will confirm this.

SELECT * FROM [dbo].[SalesOrder] WHERE [OrderDate] = '2018-01-02'
SELECT * FROM [dbo].[SalesOrder_Archive] WHERE [OrderDate] = '2018-01-02'

But the transaction’s rollback has not affected on “Create Temporary Table Temp_SalesOrder” Task, and the table still exists. Re-executing the table creation command will prove this.

CREATE TABLE Temp_SalesOrder
(
  SaleOrderId INT NOT NULL,
  CustomerId INT NOT NULL,
  SalesPersonId INT NOT NULL,
  OrderDate DATE NOT NULL,
  TaxAmt MONEY NOT NULL,
  TotalDue MONEY NOT NULL
)
GO

4th scenario: Irrelevant Transactions in a Package

Carelessness in setting up the TransactionOption feature for nested containers can lead to conditions that cause several unrelated transactions in a package. When one of the internal containers in a hierarchy of containers does not support the transaction, a separate transaction will be created for each of its upper and lower containers in the hierarchy that has the Supported feature. Committing or rollback of these transactions will start from the most internal transaction and rollback of an external transaction will not affect the internal transaction committed.
Consider the following example:

• Package ——–> Required
• Outermost Sequence Container ——–> Supported
• First & Second Sequence Container ——–> Not Supported
• Insert First & Second Record Into SalesOrder ——–> Supported
• Insert Third & Fourth Record Into SalesOrder ——–> Supported

In this example, five transactions are created for this package. One transaction will be created by the package and the other four transactions will be created for each of the Execute SQL Tasks.
Now imagine that running the second and fourth Execute SQL Task fails. The image below shows the package execution.

As said earlier, the rollback of Outmost Sequence Container Task will not affect the two transactions committed which are “Insert First Record Into SalesOrder” and “Insert Third Record Into SalesOrder”.