SSIS Error 0x80004005 Violation of Primary Key Constraint.

Applies to: SQL Server 2012 Standard Edition, SQL Server Data Tools 2012.
 

Problem Description.
 

While running a SSIS package of the "SSIS Tutorial: Creating a Simple ETL Package", I received the following error message:






I make a right click on the selected message and chose to copy the message on text format. I pasted the message in Notepad and found a more descriptive error message.


[Sample OLE DB Destination [77]] 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 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_FactCurrencyRate_CurrencyKey_DateKey'. Cannot insert duplicate key in object 'dbo.FactCurrencyRate'. The duplicate key value is (100, 20050101).".


Cause
 

As you can read on above error message, it states a primary violation has been captured trying to insert a duplicate primary key on table dbo.FactCurrencyRate, even the duplicate key values are shown on the error message (100, 20050101).

Solution
 

A few possible solutions:

1. 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. Alternatively, you can use the Aggregate Transformation task as explained here.
2. You can use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table. Click here for more information.
    

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.