0

H Guys,

I am trying to trouble a simple SSIS package

enter image description here

So the first one is querying from a source database and inserting into target staging table. Staging table is truncated every day before loading.

Below is the plan.

https://www.brentozar.com/pastetheplan/?id=ry7hu78ox

Now the target table and the source table have the same primary key which is an identity column.

About half of the time the SSIS package fails with primary key violation on the target server. I am not sure why this is happening. What are the scenarios when a query would read duplicate primary key. Or is it some issue in the way SSIS is loading data?

Initially I was thinking that a scan combined with page split is causing the duplicate read but the table which is scanning has an identity as primary key and its mostly inserts.

  • 1
    We are going to need at least the table definitions of the source and target table. If you have any more details to add about the SSIS components, please do so. – Tom V Mar 15 '17 at 9:55
0

If you are inserting into a table that uses an identity column as the primary key, I believe that the only ways you can get a duplicate primary key is:

  1. you specify to use IDENTITY_INSERT
  2. you reset the identity seed

Since you are truncating the staging table daily, you are by default resetting the identity value along with that operation. You would need to reseed the staging table after truncation to avoid this as a cause for duplicates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.