posted 5/10/2012 by nube07 - Views: [3379]
The problem:
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 UNIQUE KEY CONSTRAINT ‘Your Column’. Cannot insert duplicate key in object ‘Your Table’. The duplicate key value is (the duplicated value).”
My solution:
When loading data among tables make sure you always verify your source data. I was trying to load data to an empty table, and I kept getting the above error. As you can see the error is no specifying where the duplicated data comes from. I was getting so frustrated because my destination table was empty, so there was no way I could have duplicates ( I have also added a Look-Up transform to my data flow to avoid future duplicates) Then I was advised by my peers to check the source data, and that is when the error started to make sense. I queried and searched the source table with my “duplicated” value, and I found several instances of that value in the table. Therefore, I have added a “distinct” keyword to my select statement on the OLE DB Source transform, and this corrected the error.
The reason:
The source table had several duplicated data because it was capturing values at different times of the day, and this created a unique LOG_ID for each value. However, the destination table didn’t care for those values, but only for the Value_Name and Value_ID ( which wasn’t necessary the Log_ID value). Therefore, those values on my destination table will show as dup, and since the table has unique key constraint this caused an error every time I wanted to run the package. Well, now that is solve and my data is loaded correctly without duplicated values.