July 25, 2016 at 11:09 am
One of our SSIS package failing uncertainly, and showing error as “Primary Key violation”.
Error Description:Our SSIS package executes daily and it’s a simple TRUNCATE and load. Source and destination details are mentioned below.
Source System: source using SQL 2012 with “Always Availability ON” and source table has Primary Key constraint and possibility of duplicates is zero.
Destination system: it’s a SQL 2012 Environment and here also table have Primary Key constraint.
Recently on source system got upgraded to 2012 and enabled “Always Availability ON”, from then our packages failing uncertainly with an error message
Description: "Violation of PRIMARY KEY constraint 'PK_TableNamexxxxxxxxxxx'. Cannot insert duplicate key in object 'dbo. TableNamexxxxxxxxxxx. The duplicate key value is (95874267)."
Please provide me any solution to overcome this issue.
July 25, 2016 at 11:53 am
Assuming you're referring to Always On Availability Groups, that can't cause primary key violations. It's a high availability feature that provides secondary servers to take over if the primary fails.
Double-check your incoming data, maybe something there has changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2016 at 11:59 am
Possibility of duplicate incoming data is zero.
July 25, 2016 at 12:08 pm
One of our SSIS package failing uncertainly
What do you mean by this?
July 25, 2016 at 12:14 pm
Phil Parkin (7/25/2016)
One of our SSIS package failing uncertainly
What do you mean by this?
package failing, but not constantly.
July 25, 2016 at 12:20 pm
Siddarth V (7/25/2016)
Possibility of duplicate incoming data is zero.
I've heard that before. Often followed later by finding out that something had changed somewhere upstream and there now were duplicates
If you're getting primary key violations then there's already data in the table matching incoming, or there's duplicates in the incoming data. Availability Groups won't cause primary key violations. So in your case, either the truncate is failing, or there's duplicates in the incoming data.
Add some logging to your package to put any data failing into an errors table for analysis and to log any failures of earlier steps. That'll help pin down where the duplicates are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply