SSIS Error Violation of PRIMARY KEY constraint.

Question

Hi Expert,

I am getting error in SSIS

Violation of PRIMARY KEY constraint... can not insert duplicate records

i used the data viewer to identify the records what can be the next action

Answer 1

i used the data viewer to identify the records what can be the next action

The data viewer don't show the source data in a sorted way, the duplicate can be everywhere.
Check the primary key definition and the source data.
Optional: Create a identical table without PK, load the data and validate it for PK violation.

Answer 2

It seems like you insert or update data with value of the PRIMARY KEY which already exists in the table. A PRIMARY KEY must be a unique so this error raises.

You simply need not to change the PRIMARY KEY or insert only values that does not exists.

For example if the PRIMARY KEY is also IDENTITY then you can use INSERT without this column.

If you provide information about the table structure (queries to create the table and any constraint and index you have) then we will be able to know what can fit your case

Answer 3

Hi @Shambhu Rai ,

Please check the full error message, usually it will tell you 'The duplicate key value is (xxx, xxx)'.

Make sure the data source does not have duplicate values on the fields that are part of the OLEDB Destination primary key. To remove duplicates that exist on the data source you can use the Sort Transformation Task (SSIS Toolbox -> Common -> Sort) and make use of the checkbox named "Remove rows with duplicate sort values". You can find a practical example here.

You can use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table.

lookup-transformation-in-ssis

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.