I'm using SSIS to dump data from my SQL Server 2008 prod database to flat files and then using bulk insert to load those flat files into the SQL Server 2008 dev database. The SSIS package is running a TRUNCATE TABLE statement before the bulk insert, but when I run it I still get this error: "The statement has been terminated. Violation of PRIMARY KEY constraint. Cannot insert duplicate key." Why would there be a duplicate key after a truncate? Is there something more I need to do to clear the primary keys before the bulk insert?
Sql-server – Duplicate primary key in bulk insert after truncate in SQL Server 2008
bulkcopysql-server-2008ssis
Related Question
- Sql-server – Error while executing SSIS Package
- Sql-server – SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds
- Sql-server – Prod environment uses a different SSIS version to Dev when running a legacy SSIS package
- Sql-server – Does SQL Server Support Parallel Bulk Inserts From Multiple Connections
Best Answer
If you've truncated the table, then a primary key violation must be coming from duplicate data in the file. Try bulk inserting into a new table, without the PK constraint, and then check the table for duplicates (probably easier than writing some tool or script to parse the file directly). You can create a mimic table that won't have constraints this way:
Then change your package to reference this table, do the insert, then run:
I bet a donut the call is coming from inside the house (or the truncate is not succeeding).