Friday, April 19, 2013
SSIS : Violation of PRIMARY KEY constraint ,Cannot insert duplicate key in table
I was working on one ssis package, which was failing continuously due to the duplicate records. While Primary key constraints were defined on the table ‘Table 1’ on combination of columns ‘A1’ and ‘A2’. But due to multiple values in column ‘C1’ for each combination of (A1 and A2) column values, it was not able to insert records in table ‘Table 1’.
Earlier the table structure was as below:
But due to multiple values in column ‘C1’ for each combination of A1 and A2 column values, it was not able to insert records in table ‘Table 1’.
The solution was to include column C1 in the primary key constraints, that is to make composite primary key on column ‘A1’ ,’A2’ and ‘C1’.
For this, either one can
Drop constraint and add a new constraint on the columns A1, A2, C1 (But this will not solve the issue , as C1 column defined as NULL, and for including this column in Primary key , it should be Not Null, therefore before making changes in constraint , one should make C1 column as Not Null) As below
Or we can drop and create table, as below
Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.