Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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:

SELECT * INTO dbo.new_bulk_source
  FROM dbo.old_source
  WHERE 1 = 0;

Then change your package to reference this table, do the insert, then run:

SELECT key FROM dbo.new_bulk_source
  GROUP BY key
  HAVING COUNT(*) > 1;

I bet a donut the call is coming from inside the house (or the truncate is not succeeding).

share|improve this answer
    
The truncate was not working. It looks like the foreign keys were getting in the way, so I disabled the constraints in the query and changed the truncate to a delete. I'm puzzled why the failed truncate wouldn't show up as an error, though. –  stomcavage Aug 3 '12 at 18:51
    
@stomcavage sorry, not an SSIS guy at all, but try/catch in the package perhaps? Or some setting to ignore errors? –  Aaron Bertrand Aug 3 '12 at 19:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.