Professional SQL Server™ 2005 Integration Services

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

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