Making Transactions Work - Without Blocking

I'm having a very difficult time getting any type of transactions to work without encountering blocking.

Here's what I have. I created a package using the Import data option on a database to start with. That gave me the following:

On the control flow tab there is a Preparation SQL task which truncates a table followed by a Data Flow Task that then copies from an oracle database into that same table.

The two are connected by the green success arrow.

For obvious reasons, if the import from oracle fails, I would like the truncate of the table to be rolled back so that the table in question is returned to its previous state.

I have tried the following:

Setting the transactions setting on the data flow panel to "supported" and changing it on the Control Flow tab to required.

Setting both to required.

Setting the data flow setting to "supported", then adding a sequence container onto the control flow panel, moved both the preparation and data flow tasks into it and then set the sequence container to "required".

In all cases I have installed the package onto the server and then scheduled a job in SQL Server agent to run the package.

No matter what way I do it, the preparation sql task apparently opens a transaction and then the following data flow task starts a different one and is blocked by the first one. If you check a log file I set it to generate it clearly gets stuck after the sql preparation job when trying to start the data flow task. Checking sql server itself (Activity monitor), the job is stuck because it is blocked by process "-2".

So I'm lost as to how to make one single transaction be used for the entire package and get the behavior i need....

Thx.

R-

Robert,

I've had EXACTLY the same problem and to say I was annoyed is an understatement.

You won't like the answer - I don't think this can be solved. The -2 SPID is MSDTC (documented here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_wa-wz_3v8v.asp and here http://msdn2.microsoft.com/ms173730.aspx). It happens because the 2 tasks run under differrent connections. If they were the same connection it wouldn't be a problem.

I've mentioned a workaround here: http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx but it can only really be used in specific situations.

-Jamie

|||

Thanks for the information. Glad to know someone else has run into this also.

This really sucks. Deleting or turncating a table and then refreshing it with new data is a very common action. And it hink 100$ of the time you would want to be able to roll back the complete thing in the event of an error.

Maybe MS can explain to us how they anticpate you are supposed to perform this type of function?

|||

Thanks.

I did try that actually but received the error:

INCOMPATIBLE TRANSACTION CONTEXT

when it tried to do the dataflow task (step 2).