Package Transactions

In this part of the chapter, you will see how you can use transactions within your packages to handle data consistency. There are two types of transactions 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 using T-SQL transaction commands

Note

Here is how Books Online defines MSDTC: "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 print of what this means, but the following table will get you started with understanding the possible settings for the TransactionOption property.

Property Value	Description
Supported	If a transaction already exists at the parent, the container will join the transaction.
Not Supported	The container will not join a transaction, if one is present.
Required	The container will start a transaction if the parent has not; otherwise it will join the parent transaction.

So 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 Figure 9-20.

Figure 9-20

This package is quite basic in that all it does is create a table and insert some data into the table, and then the final task will deliberately fail. The first task contains the following as the code to be executed:

 CREATE TABLE dbo.T1(col1 int) 

The second task inserts some data into the table you just created:

 INSERT dbo.T1(col1) VALUES(1) 

To make the final task fail, you may like to try executing from this task a statement like the following:

 INSERT dbo.T1(col1) VALUES(‘A') 

Run the package with no transactions in place and see what happens. The results should look like Figure 9-21: The first two tasks succeed, and the third fails.

Figure 9-21

If you go to your database, you should see that the table was created and the data inserted, as shown in Figure 9-22.

Figure 9-22

Now you want to make sure that the table should not be created if anything in the package fails. Drop the table and start again. The first thing you want to do is to tell the package to start a transaction that the tasks can join. You do that by setting the properties of the package as shown in Figure 9-23.

Figure 9-23

You now need to tell the tasks in the package to join this transaction, by setting their TransactionOption properties to "Supported," as shown in Figure 9-24.

Figure 9-24

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. A good way to see that a DTC transaction was started is to look at the output window:

 SSIS package "Transactions .dtsx" starting. Information: 0x4001100A at Transactions: Starting distributed transaction for this container. Task failed: Insert Some Data ERROR !!! Information: 0x4001100C at Insert Some Data ERROR !!!: Aborting the current distributed transaction. Information: 0x4001100B at Transactions: Committing distributed transaction started by this container. Warning: 0x8004D019 at Transactions: The transaction has already been aborted. SSIS package "Transactions .dtsx" finished: Failure. 

Single Package, Multiple Transactions

The aim of this second package is to be able to have two transactions running in the same package at the same time. Create the package as shown in Figure 9-25. If you're not feeling creative, you can use the same statements in the tasks as you used in the previous example.

Figure 9-25

The package contains two sequence containers, each containing its own child tasks. The "Start Tran 1" container begins a transaction, and the child tasks will join the transaction. The "Start Tran 2" container also starts a transaction of its own, and its child task will join that transaction. As you can see, the task in "Start Tran 2" will deliberately fail. The "CREATE TABLE" task creates a table into which all the other child tasks of both sequence containers will insert. The idea here is that after this package has run, the table will be created and the data inserted by the "INSERT SOMETHING" task will be in the table even though the task in "Start Tran 2" fails. This could be useful when you have logical grouping of data manipulation routines to perform and they either all succeed or none of them do. The following table details the tasks and containers in the package along with the package itself and the setting of their TransactionOption properties.

Task/Container	TransactionOption Property Value
Package	Supported
"Start Tran 1"	Required
CREATE TABLE	Supported
INSERT SOMETHING	Supported
"Start Tran 2"	Required
ERROR !!!	Supported

After you execute the package, the results should look like Figure 9-26. The first container succeeded, but the second one failed because its child task failed.

Figure 9-26

If you now look in the database, you will see that the table was created and a row inserted. To prove that two transactions were instantiated, take another look at the output window:

 SSIS package "Multiple transactions same Package.dtsx" starting. Information: 0x4001100A at Start Tran 1: Starting distributed transaction for this container. Information: 0x4001100B at Start Tran 1: Committing distributed transaction started by this container. Information: 0x4001100A at Start Tran 2: Starting distributed transaction for this container. Error: 0xC00291D7 at ERROR !!!, Execute SQL Task: No connection manager is specified. Error: 0xC0024107 at ERROR !!!: There were errors during task validation. Information: 0x4001100C at ERROR !!!: Aborting the current distributed transaction. Warning: 0x80019002 at Start Tran 2: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Information: 0x4001100C at Start Tran 2: Aborting the current distributed transaction. Warning: 0x80019002 at Multiple transactions same Package: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Multiple transactions same Package.dtsx" finished: Failure. 

Two Packages, One Transaction

This example consists of two packages: "Caller" and "Called." What you want to do is to have a transaction span multiple packages. You'll have the Caller package create a table and then call a child package using an ExecutePackage task, Called, which itself will create a table and insert some data. You will then introduce an error in the Caller package that will cause it to fail. The result should be that the work done in both of the packages is undone. Figure 9-27 shows the "Caller" package.

Figure 9-27

Figure 9-28 shows the "Called" package.

Figure 9-28

As before, you will need to set the TransactionOption properties on the tasks and containers, using the values in the following table.

Task/Container	TransactionOption Property Value
"Caller" Package	Required
CREATE TABLE "Caller"	Supported
EXECUTE "Called" Package	Supported
Make Things Fail	Supported
"Called" Package	Supported
Created Table "Called"	Supported
Insert Some Rows	Supported

The point to note here is that the child package "Called" becomes nothing more than another task. The parent of the "Called" package is the ExecutePackage task in the "Caller" package. Because the ExecutePackage task is in a transaction, and the Called package also has its TransactionOption set to Supported, it will join the transaction in the parent package.

If you change the TransactionOption property on the ExecutePackage task in the "Caller" package to Not Supported, when the final task in the "Caller" package fails, the work in the "Called" package will not be undone. To see how to change the option, please refer back to Figure 9-24.

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 MSDTC. Although the example is short, it does demonstrate the fact that transactions can be used in packages that are not MSDTC transactions. Native SQL transactions will allow you a finer level of granularity when deciding what data gets rolled back and committed, but they are possible only against SQL Server. The package for this example is shown in Figure 9-29.

Figure 9-29

The reason why we have a specific task to handle the COMMIT TRANSACTION and not the ROLL-BACK TRANSACTION is that, if the CREATE TABLE Transactions task fails, a ROLLBACK TRANSACTION is issued for us.

The following table lists the contents of the SQLStatement property for each of the ExecuteSQL tasks.

Task	SQLStatement Property Value
BEGIN TRANSACTION	BEGIN TRANSACTION
CREATE TABLE Transactions	CREATE TABLE dbo.Transactions(col1 int)
COMMIT	COMMIT TRANSACTION

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 to this, you must make sure that the RetainSameConnection property on the Connection Manager is set to True, as shown in Figure 9-30.

Figure 9-30

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. You will now have a look at that happening on SQL Server by using Profiler, as shown in Figure 9-31. Profiler is really useful in situations like this. Here you simply want to prove that a transaction was started and that it either finished successfully or failed. You could also use it when firing SSIS packages to make sure that what you think you are executing is what you are actually executing.

Figure 9-31

That ends your whistle-stop look at transactions within SSIS packages, and hopefully you can take something away from this section and use it in your packages.