Have you ever run the SQL 2005 Server Import Export Wizard and noticed the identity values for the destination table are renumbered?  The SQL 2005 Server Import Export Wizard setting "Enable identity insert" is ignored when selecting "Optimize for Many Tables" causing the identity values at the destination to be renumbered.

The "Optimize for Many Tables" creates a SSIS package transferring the tables one-at-a-time within a For...Each looping structure.  It does not include the logic to enable identity insert.  Not selecting "Optimize for Many Tables" creates a SSIS package which transfers tables in parallel.

If you have many tables to transfer and you are concerned about server performance, don't select "Optimize for Many Tables" but instead set the Package property MaxConcurrentExecutables.  You may need a couple of attempts to find the balance between performance and server load.

Hope you found this helpful.

All posting are provided "AS IS" with no warranties, and confers no rights.