Home > SSIS Best Practices > Transactions in SQL Server Integration Services SSIS

Transactions in SQL Server Integration Services SSIS

There are two options to me in order to control transactions in a SSIS package. One is the plain old T-SQL using TRY CATCH to trap errors and BEGIN/COMMIT/ROLLBACK TRANSACTION to handle transactions. There is a good article here, Handling SQL Server Errors.

Another choice is to use the built-in transaction support in Integration Service with the TransactionOption (Required, Supported, NotSupported) property at package level, container level and task level. Here is a good article on that, How To Use Transactions in SQL Server Integration Services SSIS.

However, using the built-in transaction support in SSIS requires the Distributed Transaction Coordinator (MSDTC) service running on the Windows server where SSIS resides.

The Distributed Transaction Coordinator (MSDTC) service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems. MSDTC is included in Windows 2000 and later operating systems, and is also available for Windows NT 4.0.

1/23/2011: An update on my test on using TransactionOption (Required, Supported, NotSupported) property in SSIS

Result: unfortunately this solution didn’t not work for me so far. In every SSIS package I designed, I have a Execute SQL task, AUD_tblamETLAudit_OnError,  in the OnError even handler at the package level to log the error into an audit table. For some reason unknown to me, as soon as I change the  TransactionOption property at the package level from default “Supported” to “Required”, the Execute SQL task in the OnError event handler was never executed. This is a show stopper for me. By the way, I didn’t change the default transaction isolation level Serializable.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: