1

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?

  • 2
    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. – Scott Hodgin Apr 27 '17 at 13:32
  • 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. – Adán Bucio Apr 27 '17 at 14:15
  • @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 Apr 28 '17 at 4:06
  • @AdánBucio, how to work with IGNORE_DUP_KEY can you guide through that? – l.lijith Apr 28 '17 at 4:15
  • @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)". – Adán Bucio Apr 28 '17 at 23:21
2

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.

  • 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). – RDFozz Apr 27 '17 at 14:16
  • 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. – l.lijith Apr 28 '17 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.