Should I use the import and export wizard or manually create the SSIS package?

I'd like to move 26 tables from one DB to another. I see I can do this in the SSIS Import and Export Wizard. I believe the other approach would be to select tools from the toolbar in Data Flow and then configure them all.

When is it better to use the wizard and when is it best to create the package manually (with the visual tools) or programmatically? One thing I noticed with the Wizard is that it lets me select multiple tables at once, but I could not find a way to get back to that screen once the package is created, so that I could edit the various tables all in开发者_如何学C one place.

I'd say that if you can do it through the wizard, then you should do it through the wizard.

That's not because I love wizards so much, only that there are few enough things you can do through the wizard that you should save time where you can.

You cannot "edit the wizard" as a project - only the resulting package. So if you like the package for 26 tables and want to switch to 27, you have to basically recreate everyoption you chose before and choose the original 26 tables plus the new table.

You can definitely generate packages dynamically also using .NET exposed classes and methods. This may be worth the time investment. You can drive the package generation by connecting to the SQL Server and reading the regular metadata to get all the tables in a particular schema (or extra metadata you store in extended properties - get all tables with an appropriate extended property asigned).

What I would do:

First time - use the wizard

Second time - edit the package or use the wizard again

Third time - write something to generate packages (from a template)

Usually with a SSIS wizard, you can choose to save the package and not run it immediately. That way, if you wish, you can edit it. Or run it and save it, so you can edit it or run it again later.

So, use the wizard, but if you anticipate any need to modify or run the process again, save it too.

I'll add that I use the wizard for some one-time things, but usually create the package myself for things that will run on a schedule. I have much more control over what I do if I need a repeatable package.