Transactions in SSIS

It could be argued that the number 1 reason why database products like SQL Server and Oracle are a viable solution to manage data is that the products possess certain properties that ensure data integrity.  This is accomplished by applying the characteristics of ACID properties and the use of transactions.

ACID stands for Atomicity, Consistency, Isolation, Durability.  I won’t be go into detail on these in this blog but Ill direct you to this msdn link for more information: https://msdn.microsoft.com/en-us/library/aa480356.aspx?f=255&MSPPError=-2147217396 .  

The use of transactions in dealing with data is vital to ensuring data integrity and as a DBA and SQL developer I have implemented transactions in T-SQL code to ensure data integrity.  SQL Server Integration Services also needs to use transactions for the same reasons.

The use of transactions to ensure the ACID properties are followed is fully supported in SSIS and this blog will deal with the SSIS options that make it possible.

When you study for and complete any Microsoft certification on SQL server you will be required to know all of the differences between the various Isolation Levels that SQL Server supports.  These isolation level control the behavior of transactions and levels of concurrency.  This type of behavior has a direct effect on the type of behavior one can expect to see when querying data in a multi-user environment.  I won’t go into detail on the various Isolation levels because it is very complex but I will direct you to this link for further explanation.   This link will also take you to a cute poster that Kendra Little created.  http://www.brentozar.com/isolation-levels-sql-server/ .

The SSIS properties I want to discuss in this blog are the Transaction Option properties. 

 

Example:  Transaction Option in Properties window in SSDT

The three possible options for this property are Supported, NotSupported and  Required.  

This property can be set in the properties window in SSDT.

Transaction Options

      Supported:   I’ll start with Supported because this is the default that is used when you create a new package in an SSIS project.   Supported means that the task or container does not start in a transaction but it will become part of a transaction if one exists.  This means that if a container is set to Supported is part of a package that is set to Required then the container will join the transaction of the parent package.

         

      Required:  This one is a bit easier to understand.  It simply means that the container will start a transaction.  However, if the container has a parent that already has a transaction then it will just join the transaction of the parent.  If the parent were set to Supported or Not Supported then this container would start a transaction of its own.

      

      NotSupported:  This option indicates that the container will not join an existing transaction nor will it start a transaction of its own.

Note:  It is important to note that the use of transactions in SSIS require that the MSDTC  (Distributed Transaction Coordinator) be running.  

If you try to implement transactions in SSIS and receive an error like the one below then you should check to see if the MSDTC  is running.

 

Example: Error you receive if the MSDTC is not running.

To start the MSDTC you can open the Control Panel and go to Administrative Tools and click on Services.  Locate Distributed Transaction Coordinator in the list of services and click on Start in the action menu.

Example: Distributed Transaction Coordinator in the Services snap-in.

Thanks for reading,

SQL CANUCK