Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager

Did you know....

...that you can run regular SQL Server transactions in SSIS over multiple tasks without requiring the use of Distributed Transaction Coordinator (DTC)? Well I didn't but thanks to that man again, Kirk Haselden, I do now.

The default behaviour of a Connection Manager is that every task has its own connection which is why they are so named and not simply named "Connection" as in DTS2000. The RetainSameConnection property on the OLE DB Connection Manager enables you to run multiple tasks against SQL Server all within 1 SQL Server transaction if its RetainSameConnection property equals TRUE. That means that you can BEGIN TRANSACTION in one Execute SQL Task and then choose to COMMIT or ROLLBACK in another one. You can even enlist data-flows in that transaction as well!

Allow me to demonstrate.

Here's my package.

20050820ControlFlow.JPG


In the first Execute SQL Task I create myself a table and begin a transaction.

20050820BeginTran.JPG


The data-flow inserts some data into that table.

20050820DataFlow.JPG


And the second Execute SQL Task rolls back the transaction.

20050820RollbackTran.JPG

If you check the table afterwards.....there's nothing in it!

Have a go - you can download the package from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050820RetainSameConnection.zip


Here was me thinking you could only run transactions either within the same Execute SQL Task or else using distributed transactions. There's just always something new to learn about this thing isn't there!!!

-Jamie

Comments

 

SSIS Junkie said:

Here is the next in my series of posts explaining how common tasks in DTS can now be implemented in SSIS.

December 21, 2006 7:32 PM
 

Ramon said:

Hello Jamie,

Thanks for this great post. It really helped me. But I have a question - is it possible to do something like this with Oracle DB? I have a Dataflow where I insert data from SQL to Oracle and SQL Connection needs to have RetainSameConnection = TRUE. I have Oracle Services for Microsoft Transaction Server installed and I can rollback the transaction only if SQL Connection has RetainSameConnection = FALSE and TransactionOption = Required. Is there any possibility to rollback the Oracle transaction with having SQL RetainSameConnection = TRUE ?

May 7, 2007 9:57 AM
 

josh said:

Before somebody takes a flying leap off of their desk (like I was about to do yesterday), allow me to add one "little" thing to this.  The above recommendation may not work when utilizing the same Ole DB connection manager for the source and destination of a Data Flow task!!!  It took me an entire day to figure this out, but then it kind of made sense - how can you retain the same connection for both the source and destination?  So, in my case it looks like SSIS nicely opened another temporary connection for my destination - nicely sidestepping the transaction I had so carefully implemented!  The solution appears to be adding another connection to the data flow and using it for the source, which frees up the previous connection that is part of the transaction for the destination.

May 31, 2007 5:40 PM
 

Jim said:

Josh -

I believe you are right on! I was just doing some testing of a transaction I had set up and could not understand why the data was being committed even though I had forced a failure in the middle of the transaction. I too am reading from and writing to the same database. I setup a second connection manager as the 'source' and sure enough, the tranaction behaved correctly.

thanks.

August 10, 2007 8:43 PM
 

Jim said:

One more note (aka bug)...

If you have a dataflow as part of the transaction, and use the SCD component in the dataflow, you will get 'Named Pipes' errors. It seems the SCD component doesn't work inside a transaction as it needs to read from the destination before writing (inserting/updating) to it, which is blowing the transaction.

August 13, 2007 2:06 PM
 

Tolong-lah - get .NET tips & tricks said:

Oracle supports distributed transactions and MTS, through Oracle Services for Microsoft Transaction Server

October 18, 2007 7:22 AM
 

David said:

Hi

This solution works perfectly if your connection is against a SQL 2005 database. However, if your connection is against a SQL 2000 database, only the most recent SQL statement issued against the data is rolled back - in other words, on a SQL 2000 databasetry the following:

1) Begin Tran (do this in an Execute SQL task that is using a connection that has retainsameconnection property set to true).

2) Insert data into <tab>(do this in another Execute SQL task that is using the same connection as before)

3) Update a set of records that were inserted in the first step (do this in another Execute SQL task that is using the same connection as before).

4) Update a different set of records that were inserted in the first step (do this in another Execute SQL task that is using the same connection as before). But here force a failure of some kind (use raiseerror immediately after your update).

The net result is that your package exits with an error and only the update statement described in step 4) above is rolled back. The rest of the DML statements are not rolled back. It seems that only the most recent DML is rolled back. It's as if SSIS is completely ignoring the Retain same connection and implicitly creating a transactions between the two most recent statements issued against the connection with retainsameconnection set to true.

It's easy to re-produce this. Any suggestions?

David

November 1, 2007 4:01 PM
 

David said:

Oh, one more thing, I know I should just upgrade the SQL 2000 environment to SQL 2005, but that's not an option in this case - we're tied into a 3rd party vendor application that uses a SQL 2000 server. The SSIS is essentially being used to port the data to a SQL 2005 environment, but if you want to use transactions to manage what you've ported, your stuck (DTC is out because of a known limitation in SQL 2000 and the retainsameconnection property doesn't work either).

David

November 1, 2007 4:12 PM
 

Chronos said:

Is it possible to rollback transaction in a sequence container contain of several data flow task and several execute sql task that use different database connections, if one of them fail?

December 7, 2007 3:25 AM
 

jamie.thomson said:

Yes. Use distributed transactions. They are built into SSIS.

December 7, 2007 4:57 AM
 

Chronos said:

Do you mean like using transaction options in SSIS Package? I've always got error: transaction manager not available or something like that.

I've already followed the instruction in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1,

but still I've got the same error.

It isn't possible using your technique?

I've already using yours, right now I only need to use 1 data flow task and 1 execute sql task, for which if the execute task fail it rollback the data flow task only, no need to rollback the execute sql task since it failed. (I imported 1 row from database A into database B, and if it's successfully executed, the row in database A which hold the status is updated to 1, meaning that this row doesn't need to be processed in the next batch of process)

What I'm thinking is what if I need more tasks than that in the future, and I need to rollback all of them when only one of the task failed?

thanks for the fast response :)

December 7, 2007 6:01 AM
 

Divyesh Sah said:

Adding Another Caveat To This:

If you using the same connection manager as the one you are using in transaction for destination you will face blocking of your scripts along with the SQL Logging procedure. Your package will just be stuck on that task with yellow color. You might not be able to debug this from the package.

You can verify this from  connection in SQL Server.(Using Sp_who2)

The Solution is, If you are using SQL Server Logging make sure you create another connection manager for this.

March 24, 2008 11:10 AM
 

Prashant Shekahr said:

I am currently using Slowly Changing Dimension(SCD) for Insert/Update record. The package has transaction enabled. The strange thing that is happening is that the Transaction is working in some cases while in other cases it is not working at all and my packages just hangs. When I remove the Transactions everything is working fine. The cases where the transaction failed for the SCD is when the table contained less number of records then the data files. For e.g. when there is no data in the table but still i choose SCD. Also when say for e.g. the table contains 1000 rows and my data file contains 3000 rows. Could you please suggest the possivle cause for the same?

June 2, 2008 2:08 PM
 

jamie.thomson said:

Prashant,

I suspect there is blocking occurring at the destination. Run sp_who2 when your package hangs and see if there are any processes being blocked by SPID=-2. if there are then you've found your culprit

-Jamie

June 2, 2008 2:38 PM
 

Dan Kennedy said:

Can I just thank both Jamies (excellent blog btw) and josh.  I've been struggling with a transactional package that kept hanging during an insert and the blocking spid was -2.  Separate connection managers to the same database for both source and destination sorted it.

Thanks again.

June 20, 2008 12:41 PM
 

Matt said:

Just an FYI.  SQL Script Transactions will not work with conditional splits or multicasts.  I believe this is because SSIS creates new connections for each destination.  The new connections bypass the original begin transaction statement on the original connection.  I have not found a work around for this yet.

I have not tried this with DTC turned on.  

September 16, 2008 9:36 PM
New Comments to this post are disabled

This Blog

Syndication

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