There seems to be a BUG in BIDS when developing SSIS packages using the Import/Export Data wizard.
If you use the wizard to import a large number of tables, and then select all the tables, and then choose to delete exisiting data in each table, the PrologueSQL file does NOT get built correctly. Instead of having a
TRUNCATE tablename Go
for each table, it just has a bunch of "Go"s with nothing between them. In the step immediately prior, where you confirm what the wizard will do, it tells you, after each table, that it will delete any existing data...but it doesn't do this.
If, during the wizard, I select each individual table one at a time and tell it to delete existing data, then it will get built correctly, but not if I select them all at once...YET, if I do select the whole block, choose delete existing data, and then select any single table, it shows that table as being set up to delete existing rows.
This is very frustrating when trying to import large numbers of tables.
Am I missing something? or is this really a bug?
Thanks, Jeff
Jeff,
We've noticed the same behavior here. If we edit the mappings for each of the tables individually we see that the 'Delete rows in destination table' is selected (after selecting that option for the group). If we click OK for the mappings dialog the TRUNCATE options is then correctly added to the package.
I vote for bug.
Ray
|||Post it at:http://connect.microsoft.com/sqlserver/feedback|||
Yes, this is a known bug.
The best workaround I can offer is to unselect the "Optimize for Many Tables" checkbox. The transfer will probably succeed if the number of tables is resonable (say less than 100) for your hardware (transfers will go in parallel). If it fails, try to go in multiple batches with smaller number of tables.
Thanks.
