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).”
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 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.