Mindmajix

Package Transactions

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 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.

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 screen shot.

create the simple package

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”:

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

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

Run the package with no transactions in place and see what happens. The results

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

Database

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.

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.

by setting their TransactionOption properties to Supported

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:

SSIS package “C:\ProSSIS\Code\Ch15\04_TransactionDemo.dtsx” starting.
Information: 0x4001100A at 04_TransactionDemo: Starting distributed
transaction for this container.
Error: 0xC002F210 at Insert A, Execute SQL Task: Executing the query
“INSERT dbo.T1(col1) VALUES(“A”)” failed with the following error:
“Invalid column name ‘A’.”. Possible failure reasons: Problems with
the query, “ResultSet” property not set correctly, parameters not set
correctly, or connection not established correctly.
Task failed: Insert A
Information: 0x4001100C at Insert A: Aborting the current distributed
transaction.
Warning: 0x80019002 at 04_TransactionDemo: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (1) 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 04_TransactionDemo: Aborting the current
distributed transaction.
SSIS package “C:\ProSSIS\Code\Ch15\04_TransactionDemo.dtsx” finished:
Failure.

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 screen shot. If you’re not feeling creative, you can use the same statements in the tasks that you used in the previous example.

Create the package

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.

TASK/CONTAINER TRANSACTIONOPTION PROPERTY VALUE

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

After you execute the package, the results should look

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:

SSIS package “C:\ProSSIS\Code\Ch15\05_MultipleTransactionDemo.dtsx”
starting.
Information: 0x4001100A at Trans 1: Starting distributed transaction for this container.
Information: 0x4001100B at Trans 1: Committing distributed
transaction started by this container.
Information: 0x4001100A at Trans 2: Starting distributed transaction for this container.
Error: 0xC002F210 at Insert A, Execute SQL Task: Executing the query
“INSERT dbo.T1(col1) VALUES(“A”) ” failed with the following error:
“Invalid column name ‘A’.”. Possible failure reasons: Problems with
the query, “ResultSet” property not set correctly, parameters not set
correctly, or connection not established correctly.
Task failed: Insert A
Information: 0x4001100C at Insert A: Aborting the current distributed
transaction.
Warning: 0x80019002 at Trans 2: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (1) 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 Trans 2: Aborting the current distributed transaction.
Warning: 0x80019002 at 05_MultipleTransactionDemo: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (1) 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 “C:\ProSSIS\Code\Ch15\05_MultipleTransactionDemo.dtsx”
finished: Failure.

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 screen shot shows the TransactionParent package, and  second screen shot shows the TransactionChild package.

the TransactionParent package

The TransactionChild

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

TransactionOption property

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.

If you change the TransactionOption property on the Execute Package Task in the TransactionParent package to Not Supported (refer to below screen shot), 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 screen shot.

Single Package Using a Native Transaction package example

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:

SQLSTATEMENT PROPERTY VALUE

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.

package transactions

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 screen shot. 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.

SQL Server by using Profiler

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.


0 Responses on Package Transactions"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.