SSIS: Update & Insert to same table in Data Flow task causing “This operation conflicts with another pending operation on this transaction. The operation failed.”

  • Article

Consider below SSIS Package,

Single Data Flow task which has

è Source , which fetches data from Table “xyz”

è Lookup which looks up in table “abc” and finds rows which are already there.

è For new rows look up will push them to no-match output and that will be mapped to OLEDB destination.

è For rows which are matching we will do update on specific column.

 

For OLEDB destination we are using Fast Load option.

Now when we run this package we get below error.

[OLE DB Command [39]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "This operation conflicts with another pending operation on this transaction. The operation failed.".

 

Package:

 

So what is going wrong?

We saw that we have table lock property checked, this could be issue as if Insert will have lock on table update will not be able to modify any rows.

We disabled “Table Lock” property but still we faced same error.

Next I took profiler to see what is happening in backend,

Profiler entries:

SQL:BatchStarting  insert bulk [OrdersNew]  62       2013-12-03 19:49:44.967         

SQL:StmtStarting   insert bulk [OrdersNew]  62       2013-12-03 19:49:44.970         

RPC:Starting exec sp_execute 2,187.4100,10395    59       2013-12-03 19:49:44.970         

SP:StmtStarting      update [dbo].[OrdersNew] set Freight = @P1 where OrderId= @P2    59            2013-12-03 19:49:44.970         

Exception      This operation conflicts with another pending operation on this transaction. The operation failed.     59       2013-12-03 19:49:44.970         

 

So we see that there are two queries started one for bulk insert and other for doing update. And as soon as update starts it gives exception.

I couldn’t find why it is not able to do update even though insert does not have table lock, however I was manage resolve this error.

How?

Use normal “Table or View” for insert and not “Fast Load” option.

So this fast load option is causing this issue.

 

Hmmm…. May be someone can find root cause for this but if you couldn’t we have solution…

 

We have seen that this has not caused any performance impact as such on package so should be viable solution.

 

So that’s all for today!!!!

Let me know if you have any questions about this post.