Package Transactions - SSIS

 

Package Transaction

This part of the Reliability and Scalability Topic describes how you can use transactions within your packages to handle data consistency. Two types of transactions are available in an SSIS package:

• Distributed Transaction Coordinator (DTC) Transactions: One or more transactions that require a DTC and can span connections, tasks, and packages
• Native Transaction: A transaction at a SQL Server engine level, using a single connection managed through the use of T-SQL transaction commands

NOTE Here is how Tutorial Online defines the Microsoft DTC: “The Microsoft Distributed Transaction Coordinator (MS DTC) allows applications to extend transactions across two or more instances of SQL Server. It also allows applications to participate in transactions managed by transaction managers that comply with the X/Open DTP XA standard.”

You will learn how to use them by going through four examples in detail. Each example builds on the previous example, except for the last one:

• Single Package: Single transaction using DTC
• Single Package: Multiple transactions using DTC
• Two Packages: One transaction using DTC
• Single Package: One transaction using a native transaction in SQL Server

For transactions to happen in a package and for tasks to join them, you need to set a few properties at both the package and the task level. As you go through the examples, you will see the finer details behind these transactions, but the following table will get you started by describing the possible settings for the TransactionOption property.

Armed with these facts, you can get right into the thick of things and look at the first example.

Single Package, Single Transaction

To start the first example, create the simple package shown in below screenshot.

This package is quite basic in that all it does is insert some data into the table and then the last task will deliberately fail. Open SSMS and run the following code on the AdventureWorksDW database:

CREATE TABLE dbo.T1(col1 int)

In the Execute SQL Task named “Insert 1”, use the following code in the SQLStatement property to insert data into the table you just created:

INSERT dbo.T1(col1) VALUES(1)

To make the final task fail at runtime, use the following code in the SQLStatement property of the Execute SQL Task names “Insert A”:

 

Check Out SSIS Tutorials

 

INSERT dbo.T1(col1) VALUES(“A”)

Run the package with no transactions in place and see what happens. The results should look like below screenshot: The first task succeeds, and the second fails.

If you go to your database, you should see data inserted, as shown in below screenshot.

Now run the following code in SSMS to delete the data from the table:

TRUNCATE TABLE dbo.T1

Next, you want to set up the package to start a transaction that the tasks can join. You do that by setting the properties of the package, as shown in below screen shot. Set the TransactionOption property to Required

You now need to tell the tasks in the package to join this transaction, by setting their TransactionOption properties to Supported, as shown in below screen shot.

NOTE To quickly set the properties for all these tasks at once, select them by holding down the Ctrl key and clicking on each task, and set the TransactionOption property to the desired value.

Now when you re-execute the package, a DTC transaction will be started by the package, all the tasks will join, and because of the failure in the last task, the work in the package will be undone. Go back to SSMS and query the table. You should see no data in it. A good way to see the DTC transaction that was started is to look at the output window in SSIS:

Single Package, Multiple Transactions

The goal of this second package is to have two transactions running in the same package at the same time. Create the package as shown in below screenshot. If you’re not feeling creative, you can use the same statements in the tasks that you used in the previous example.

The package contains two Sequence Containers, each containing its own child tasks. The Trans 1 Container begins a transaction, and the child tasks join the transaction. The Trans 2 Container also starts a transaction of its own, and its child task joins that transaction. As you can see, the task in Trans 2 will deliberately fail. A real-world purpose of this scenario could be the logical loading of different sets of data. This could be useful when you have logical grouping of data manipulation routines to perform, and either all succeed or none of them succeed. The following table shows the tasks and containers in the package, along with the package itself and the setting of their TransactionOption property.

After you execute the package, the results should look like below screenshot. The first container succeeded, but the second one failed because its child task failed.

If you now look in the database, you will see that the numbers 1 and 2 were inserted. To prove that two transactions were instantiated, take another look at the output window:

Two Packages, One Transaction

The third package in this series will highlight a transaction that spans multiple packages. Specifically, there will be two packages: “TransactionParent” and “TransactionChild.” The TransactionParent package will insert two rows into a table and then call the TransactionChild package using an Execute Package Task, which itself will insert two rows into the same table. You will then introduce an error in the TransactionParent package that causes it to fail. As a result, the work done in both packages is undone. Below first screenshot shows the TransactionParent package, and the second screenshot shows the TransactionChild package.

As in the previous example, you need to set the TransactionOption property on the tasks and containers. Use the values in the following table:

he point to note here is that the TransactionChild package becomes nothing more than another task. The parent of the TransactionChild package is the Execute Package Task in the TransactionParent package. Because the Execute Package Task is in a transaction, and the TransactionChild package also has its TransactionOption set to Supported, it will join the transaction in the TransactionParent package.

 

Frequently Asked SSIS Interview Questions & Answers

 

If you change the TransactionOption property on the Execute Package Task in the TransactionParent package to Not Supported (refer to below screenshot), when the final task in the TransactionParent package fails, the work in the TransactionChild package will not be undone.

Single Package Using a Native Transaction in SQL Server

This example differs from the others in that you are going to use the transaction-handling abilities of SQL Server and not those of MS DTC. Although the example is short, it does demonstrate that transactions can be used in packages that are not MS DTC transactions. Native SQL transactions provide you with a finer level of granularity when deciding what data is rolled back and committed, but they are possible only with SQL Server. The package for this example is shown in below screenshot.

Although you cannot see it because the screenshot is black and white, the workflow line from the Create Table Transactions Task to the Rollback Task is red, indicating failure; however, you can see the word failure next to the precedence constraint line.

The following table lists the contents of the SQLStatement property for each of the Execute SQL Tasks:

The key to making the package use the native transaction capabilities in SQL Server is to have all the tasks use the same Connection Manager. In addition, you must ensure that the RetainSameConnection property on the Connection Manager is set to True, as shown in below screen shot.

When the package is executed, SQL Server will fire up a transaction and either commit or rollback that transaction at the end of the package.

Now have a look at that happening on SQL Server by using Profiler, as shown in below screenshot. Profiler is very useful in situations like this. Here you simply want to confirm that a transaction was started and that it either finished successfully or failed. You can also use Profiler when firing SSIS packages to ensure that what you assume you are executing is what you are actually executing. Explaining how to use SQL Server Profiler is beyond the scope of this tutorial, and more information can be found in SQL Server Tutorial Online.

That ends your whistle-stop look at transactions within SSIS packages. Next, it’s time to look at error outputs and how they can help with scalability.

 

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator