Hi!
I'm afraid your workaround doesn't work. When you try to copy data from multiple tables joined by foreign keys you will get missing key errors if you don't use "Optimize for many tables" option.

My impression over SSIS is very very poor. So far everything I try to do with it doesn't work at all or doesn't work as expected.

I've encountered this problem as well and I'd really like to get it fixed.

To make microsoft fix this, please vote for feedback item 135905 at connect.microsoft.com under the SQL server feedback connection!

/Daniel

I don't think the "Optimize for many tables" causing that trouble. I tried many times even only import one table, the default setting still keep Enable identity insert unchecked. It is really annoying.

I am exporting from SQL 2005 to SQL 2000. I am only doing one table with "Optimize for many tables" unchecked. "Enable Identity Insert" is also checked for the single table I am copying. The preview correctly shows the Primary Key column jumping from 71 to 73 (I deleted a row). However, after running the export the SQL 2000 table has an entry for 71 and 72. It doesn't matter what is checked, this always happens. Is there another way around?

I needed to copy a complete database from 2005 to 2000

This is how I did it - Assuming the table(s) you need in 2000 are all created with indexes, constraints etc but contain no data.

Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

Then use the export wizard on 2005,

select all tables and views

Edit Mappings and check the Enable Identity Insert box

UN check the Optimise for many tables box

Deselect ALL the views so that it is only tables that are copied

Run the export ignoring the warning

After the export is complete

Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

This has ceratinly worked on the table I was having trouble with - I still need to check the rest but it is looking promising

I hope this helps

PingBack from http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/

After spending a day fighting with this I learned about SqlBulkCopy from a post on David Hayden’s blog and created this quick app to get around the problem.

It can copy data and keep identity and nulls intact. Used with the generate script wizard you can make a complete database copy as long as you have sql access.

It’s like having the SQL 2000 wizard back:)

You can get it here:

http://projects.c3o.com/files/3/plugins/entry11.aspx

PingBack from http://rocky.finestmatingstories.com/identityinsert.html