Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Package transactions

DTS 2000 gave us the ability to run transactions over our packages that enabled us to fail or succeed whole groups of atomic units of work...much in the same way that you can do using a traditional RDBMS. Transactions can even be run over multiple packages, over multiple data destinations, enabling us to commit or rollback very large groups of operations. Mind you, only 1 transaction can be run per package.

SQL Server Integration Services includes similar functionality although it is now much easier to configure, largely due to the new architecture. It also enables you to have multiple transactions in a package. Each container has a property called TransactionOption which is used to configure that container's participation in a transaction. If a container starts a transaction all child containers of that transaction have the option to enlist in that transaction. TransactionOption has 3 possible settings:

  • NotSupported - The container will not enlist in a transaction, even if one was started by a parent container.
  • Supported - The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a transaction the container will not be enlisted in any transaction.
  • Required - The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a tranasction the container will start one itself.

I envisage that transactions will be used heavily with checkpoints in order to control package execution. Checkpoints are SSIS's method of package recovery in the event of failure. Note that, by design, a checkpoint will not function if it is placed on a container that enlists in a transaction started from a parent container.

Note that SSIS's support for transactions relies on Microsoft Distributed Transaction Coordinator (MS DTC) which must be running on the same machine that is running the SSIS package(s).

This is a very potted summation of how transactions are configured in SSIS. Look out for a more detailed overview, including test cases, coming out soon on SQLServerCentral.com!

Kirk has a good discussion around transactions on his blog.

Comments/questions are welcomed!

- Jamie

 

Published Tuesday, December 14, 2004 1:13 PM by jamie.thomson

Comments

 

TrackBack said:

January 3, 2005 10:12 AM
 

TrackBack said:

January 4, 2005 3:43 PM
 

TrackBack said:

January 31, 2005 2:11 PM
 

TrackBack said:

January 31, 2005 2:12 PM
 

Pesho said:

Very nice
September 1, 2005 9:21 PM
 

prasanta said:

I need to maintain the distributed transaction using MS DTC in SSIS package between 2 servers ( 1 present at Corpnet microsoft Domain and the other at Extranet Microsoft Domain).

Note: It's working fine for me if both are present at Corpnet. I am able to set the Sequence container Transaction Property to = Required and the inner task Transaction property=Supported. But the same case is failing if I am going for cross domain saying the error: [OldPartySource [1]] Error: The AcquireConnection method call to the connection manager "<connection manager Name>" failed with error code 0xC0202009.

can you help me in this?

Replay me on pkjena@rediffmail.com

Thanx & Regards,

Prasanta

July 24, 2007 2:59 PM
 

Venk said:

Hi Even m facing similar problems. I get a error message:The transaction has already been implicitly or explicitly committed or aborted. How do i prevent this. My transaction spans across two servers. MSTDC is running in both Machines, i have set certain properties also but still this error message comes up.

November 20, 2007 12:02 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems