Error while executing SSIS Package

Question

When I tried executing a SSIS Package I came across following error.

[OLE DB Destination [148]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_user'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (-3)."

I was trying to pull data from one table to another table across two DB servers. How can I proceed further?

• Scott Hodgin - Retired: The error seems pretty clear to me. You are trying to insert -3 from the source table to the target table and that value already exists in the target table. It might be possible that the source table actually allows duplicates for this data and the target does not due to having a primary key for that data.
• Adán Bucio: It might be the case that -3 does not exist in the target table, but the incoming data has duplicates. In which case a Sort Transformation with remove duplicates would be apropiate. Or since the target is SQL Serve you could configure the PK_user constraint to ignore duplicates with the IGNORE_DUP_KEY option.
• l.lijith: @ScottHodgin,Thank you for your response. Issue is related to the PK,Since I have multiple tables to move I am facing the same error. I have PK in source and not in target.
• l.lijith: @AdánBucio, how to work with IGNORE_DUP_KEY can you guide through that?
• Adán Bucio: @l.lijith You have to drop and recreate your primary key. You set this option the same way you set other index options "WITH(IGNORE_DUP_KEY = ON)".

Answer 1

Looks like you are trying to insert duplicate PK_user IDs into your destination table, that is set to be unique as well.

You'll need to join the 2 sets and only write the unique PK_user results or remove the unique constraint on your destination table.

• RDFozz: Or, if you have distinct records with the same primary key, change the duplicate primary key values to something unique (all related tables that use the value as a foreign key would also need updated, if relevant).
• l.lijith: Thank you for your response. Issue is related to the PK,Since I have multiple tables to move I am facing the same error. I have PK in source and not in target.There are around 40 tables to be moved.Conflict between PK in source and destination.