SSIS - Wizard vs manual vs programming

views:

136

answers:

SSIS - Wizard vs manual vs programming

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 one place.

+1 A:

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.

John Saunders 2010-02-04 00:25:49

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.

thursdaysgeek 2010-02-04 00:51:51

How would you edit it? Is there a way to view the created package through the wizard interface after saving it?

alchemical 2010-02-04 00:53:49

When you have the option to save the SSIS package, save it as a .dtsx to the file system. Then, create or open a solution in Visual Studio, and add that package to the solution. From there, you can do whatever you want, and when it's all working the way you want, you can usually either run it directly from studio, or import it back into Integrated Services and call it from a job, or whatever you need to do. It's awkward editing in VS, instead of how easy it was to edit a DTS package right in SQL, but it's the same type of easy visual interface.

thursdaysgeek 2010-02-05 00:52:58

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.

HLGEM 2010-02-04 16:17:29

Nice stuff here. Thanks a lot for this. If you want to learn more about programming SSIS packages in C# .net and VB .net from the below link: http://www.sqllion.com/2009/05/ssis-programming-basic/ Thanks,

SQL Lion 2010-04-27 16:21:41

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)

Cade Roux 2010-04-27 16:26:45

ansaurus

tags:

views:

answers:

SSIS - Wizard vs manual vs programming

related questions