SSIS transactions

Jul 21, 2010 at 2:16 PM
This relates more to SSIS generally than KSCD. My dataflow requires a transaction. When a KSCD has type 2 updates and I have data coming out of new scd2 and expired scd2 outputs the data flow will fail with the following error message: "This operation conflicts with another pending operation on this transaction. The operation failed.". It seems as though the update is conflicting with the insert. When I turn off transactions this works. I have done quite a bit of research around SSIS destination types and connection types. I have not found any information saying running an insert on the same table as a simultaneous update query should fail, when using a transaction. Both destinations are OLE db destinations. The insert destination has 'Table Lock' turned off. Anyone encountered this before?
Dec 1, 2010 at 8:45 PM

I'm no expert on how MSDTC acquires and handles locks and all that.  I have run in to similar issues before, and they always get fixed when you use another (unrelated) best practice.

Stop using the OLE DB Command component.  Instead, use the following pattern wherever you want to use one:

  1. Place an Execute SQL Task before your Data Flow.  In that task, create a table in the SQL Server that contains the key columns used for the update, as well as columns for all values you're going to update.
  2. In your data flow, use an OLE DB Destination to insert rows into that newly created table.
  3. Place another Execute SQL Task after your Data Flow.  In that task, use an UPDATE statement to update the contents of your dimension table with the new values in your newly created (and filled) table.

Doing that will fix your transaction issue - because you're inserting to two different tables, THEN updating after all inserts are complete.  In addition, it will be faster, because an insert component is faster than the command, and the UPDATE is a set-based operation too.

Dec 2, 2010 at 10:00 AM
Edited Dec 2, 2010 at 10:01 AM

Funnily enough I have been having the same issue over the past couple of days but I am using the BatchDestination component (which does what todd suggested just in a single dataflow component so not in the order Todd suggests)

What is strange is that it used to work and now it isn't working.

I have had to turn of transactions for the moment to keep moving. I had modified the BatchDestination component and that may have been the cause of the problems but I haven't been able to spend the time troubleshooting yet.

Give the stock BatchDestination a go and see if this solves the problem.

Please report back if you try this so I can add your experience to mine