We help IT Professionals succeed at work.

SSIS Error - "This operation conflicts with another pending operation on this transaction. The operation failed."

thomaszhwang
thomaszhwang used Ask the Experts™
on
I have a Data Flow Task.  I have set its Transaction Option to Required.

This Data Flow Task is basically used to load data into a fact table.  

Before the data is inserted into the fact table, I simply do a lookup to see if the row is already in the fact table.  If it is already there, rows will be redirected to an OLEDB command which does an update to the corresponding row.

However the OLEDB command failed with the error - "This operation conflicts with another pending operation on this transaction. The operation failed."

Is there a work around?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
A workaround could be to issue first a delete for the duplicates using a query with "WHERE EXISTS" followed by the INSERT.
When wrapped within the BEGIN TRANS and COMMIT TRANS as described in the link 25112 posted, this should work.

Author

Commented:
Are you saying the INSERT rows are later UPDATED in my package?  I think this might not be possible in my case, but I will double check.
Commented:
When your update is the replacement of all fields in the record, than the delete approach will work.

Otherwise execute the insert and ignore the errors for duplicate records and next perform an UPDATE query using the WHERE EXISTS clause to change the field(s) that need to be updated.

Author

Commented:
Thanks