I am trying to trouble a simple SSIS package
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.
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.