Running SQL 2005 SP4
I am trying to use the Import/Export Wizard to copy 40 table from Server A to Server B. After selecting all the tables, I set them all to "Delete Rows In Destination Table" and "Enable Identity Insert".
I deselect "Optimize for Many Tables" so I can save the package & edit it later.
When I run the package as the last step in the Wizard, it fails and tells me I cannot insert a duplicate - Violation of primary key.
There are no duplicates in the source. "TableA" only had 1 record anyway !
When I run it again, "TableA" completes cleanly, but now I get an error that "TableC" has duplicates.
It seems that the truncates and copies are running at the same time, so sometimes a copy is happening before the truncate and throws an error, and sometimes after the truncate and completes normally.
Any thoughts on this crazy situation ?
EDIT: I saved the package, opened it in BIDS, and see that only 4 of the tables have a "truncate" statement even though in the GUI, they all have "Delete Rows In Destination Table" checked ?!?!? WTF ?!?!
EDIT2: I didn't manually check "Delete Rows In Destination Table" on each table. I highlight all the tables, then choose "Edit Mappings" and check "delete" 1 time and it applies to all the tables ..... except I guess it doesn't really apply to all the tables, just LOOKS like it does, since the check mark is there, but it does not execute.
Is there a workaround for this bug ???