Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Beware of transactions

Regular readers of my blog may have read about the investigative work I have been doing around transactions in SQL Server Integration Services and may also have downloaded my example package from here.

Recently I was demo-ing this package to some of my colleagues at Conchango and suffered the ignominy of the demo falling flat on its face when one of said colleagues said "What happens if you change blah blah blah..." The package hung and I didn't have a clue why...which was quite embarrassing. When I investigated why it fell over I found out some interesting things that are worth sharing.

The package in question had 4 tasks:

  1. A DDL task to create a table
  2. A DML task to insert a valid value
  3. A DML task to insert an invalid value
  4. A DML task to insert a valid value

My demo was to show the affect of tasks 2,3 & 4 when I included them (or not) in an SSIS transaction. The full results of this can be read at the above link to SQL ServerCentral.com. The change that my colleagues asked me to make was to include all 4 tasks (including the DDL transaction) in the transaction and this is where the problems started. The package hung on task 3 (the faulty insertion).

We had a look at activity on the SQL Server using sp_who2 and were able to determine that the process being run by task 3 was being blocked. Killing the blocking process caused the SSIS package to fail gracefully, as was previously expected.

After further investigation it turned out that the blocking process was being launched by task 1, the DDL task. We were able to prove in a seperate test that SSIS can successfully handle DDL statements in a transaction (as was expected) but clearly something was awry here.

To cut a long story short this is not a bug that can be fixed but something to be aware of when designing SSIS packages. i.e. Tasks in an SSIS transaction can be blocked by tasks in the same transaction thus preventing them from getting rolled back. The lesson to be learnt (and what I have taken away from this) is that using transactions in an SSIS package should be thought about and not used as a panacea to all potential problems. Plan your packages to use transactions and rollback but place your rollback points at specific, planned milestones in your execution control-flow.


Published Monday, January 31, 2005 2:11 PM by jamie.thomson



jamie.thomson said:

re: "The lesson to be learnt (and what I have taken away from this) is that using transactions in an SSIS package should be thought about and not used as a panacea to all potential problems."

Jamie, I take on board this but I still have some concerns. I am not an MTS (Microsoft Transaction Server)/DTC expert..

But from what you tell me, when SSIS enlists a Transaction, it will be managed by MTS/DTC. This appears to be thread based, and the client code (SSIS in this case) cannot guarantee to use the same thread within MTS/DTC in the same package.. Thus in your sample.. task A will be using thread 1, and task B,C and D will be using thread 2. If there is a problem, then you get the hang, because something in thread 1 is 'blocking thread 2'

The issues I have with this are: why does it appear that all the DML statements using 1 thread and the DDL statements seem to be using thread 2?

And anyway.. Hold do you code around this, if you do not know what is or will happen in MTS/DTC (which is out of your control!)

I have looked around this subject

2 internet articles MAY provide an answer. Item 10: What about syncronization issues in MTS ?




I must state this could be a poisson rouge.. But is MTS/DTC involves the SPM (Shared Property Manager) then you may be getting this issue..

The interesting bit is
"Locking and Concurrency
A noteworthy limitation with both the SPM and the ASP Application object is their inability to use shared locks. Both the SPM and the ASP Application only offer exclusive locking. Once a client request acquires an exclusive lock for a particular data item, it blocks every other client until the lock is released. In many situations, exclusive locking is overkill"

Also further on
"The SPM typically releases its locks as soon as the current method call finishes before the distributed transaction coordinator (DTC) starts to run the two-phase commit protocol. This lack of synchronization can lead to violations of the ACID rules."

February 7, 2005 9:22 AM

jamie.thomson said:

This may have relevance - Check out Smart Transactions with SQL Server 2005

I wonder if the first DDL is lightweight, the subsequent one becoming promoted to a distributed transaction?

February 7, 2005 9:42 AM

Gergana said:

Very nice
September 2, 2005 5:18 AM

ruby agarwal said:

Hello jamie,

I am facing problems while using transactions. I am using a foreach loop inside which i am doing following things:--

1)load data from a file to a temporary table

2)if this load (1) is successful then truncate the data from the main table

3)Move the data from temporary table to the main table

4)archive the file

i want to use transaction at step 2 and 3.In case step 3 fails then the truncation step shold roll back and data should be back to main table.

I tried using transaction by setting transaction option for the package to requird and for the foreach loop container to supported but i am getting this error

"[Connection manager "NHQGLBDM001.DHL"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.". "

I have enabled DTC(distributed Transaction coordinator ) service.

Any help on this...



April 21, 2008 8:58 AM

jamie.thomson said:


I don't think enabling DTC is enough - you actually have to configure it as well. Not sure how though.

DTC isn't my strong point I'm afraid.


April 21, 2008 10:36 AM

ruby agarwal said:

Thanks Jamie...

I tried configuring the settings but afraid it was not successful.

For Refernece


Instead of using this method i included a ExecuteSQL task and included the BEGIN END TRANSACTION and did the commit Rollback Insert between this.Its working fine.

-- Ruby

April 23, 2008 2:59 PM

Reza said:

Hi Jamie,

I wonder if you could help me on the following issue with Transactions:

I'm trying to use two transactions in a package. Here is the scenario

Package - transaction option is "supported"

Sequence container A - transaction option is "required"

Sequence container B - transaction option is "required"

Connection manager connects to an Oracle database.

The problem is with the above settings when execution reaches the first SQL Execute Tasks in container A it failes with the following error message:

[Execute SQL Task] Error: Failed to acquire connection "RealSuiteDestination". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I change transaction option to "supported", then the sequence containers work fine. I need two transaction to isolate two scenarios that exists in the sequence containers.

I'd appreciate your reply,


June 10, 2008 8:37 PM

jamie.thomson said:

Transactins in SSIS use MSDTC so you'll have to make sure that is configured for your Orcle server.

MSDTC is out of my sphere of knowledge I'm afraid.

June 10, 2008 8:52 PM

Reza said:

Thanks a lot for the hint!

June 10, 2008 10:17 PM
New Comments to this post are disabled

This Blog


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