Cannot insert duplicate record in a table

I am trying to execute stored proc through SSIS and it gives me following error:

[Execute SQL Task] Error: Executing the query "Exec sp1 ?" failed with the following error: "Procedure: sp1 Line: 66 Message: Cannot insert duplicate key row in object 'table.sq1' with unique index 'UIX_sp1_Key'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection开发者_运维知识库 not established correctly.

Actually the stored Proc sp1 is truncating & reloading a data into a table. I am not able to figure out where exactly its trying to insert duplicate record.

Thanks in advance.

Your data must have duplicate values across the key column(s). You could remove the primary key temporarily (or create another table with the same structure but without the definition of the primary key, but that would mean changing the stored procedure), then load the data into the table. Then use this SQL statement:

select keycol1 {,keycol2 {,keycol3} ...}, count(*)
from tablename
group by keycol1 {,keycol2 {,keycol3} ...}
having count(*) > 1

That will show you the data values that are duplicates across the key column(s).

If you are truncating the table before load, then you must have duplicate data in the source.

Examine this to see what there is. use Excel or Access or some such if needed to assist. Or drop the unique constraint then query the staging table with an aggregate query.