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?

Improve this question
5
  • 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 - Retired
    Commented Apr 27, 2017 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
    Commented Apr 27, 2017 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
    Commented Apr 28, 2017 at 4:06
  • @AdánBucio, how to work with IGNORE_DUP_KEY can you guide through that?
    – l.lijith
    Commented Apr 28, 2017 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
    Commented Apr 28, 2017 at 23:21

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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
    Commented Apr 27, 2017 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
    Commented Apr 28, 2017 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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