SSIS : Violation of PRIMARY KEY constraint ,Cannot insert duplicate key in table

Scenario:

 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’.

Question: Can we modify Primary key constraint??

Answer: No

Error: Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object Table1

Solution:

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.