Copying multiple tables using SSIS Package

Question

I am trying to design an SSIS package which copy about 50+ tables from an ODBC DataSource (QuickBooks DB) to an SQL DB. Should I create 50 Data Flow Task to do this ? What is the best way to do this ? Putting DFT inside a Loop, and reading the tables ? Or 50+ Data Flow Tasks ???

Answer 1

You can create 50 Data Flow Tasks, but you don't have to.

It is possible to have multiple independent sources-destinations in the same DFT. This will be not as flexible, because you can run single DFT separately from the package (while debugging), but you cannot run a piece of DFT without modifying it (as far as I know).

Depending on which option you choose, I see a couple of ways to save yourself from mundane work with 50+ tables:

a) Let SQL Server Import and Export Wizard do the boring work for you. The best about this tool is that it can create a .dtsx package.

So, with the wizard, you can:

• select for importing all 50+ tables from ODBC DataSource
• instead of running the wizard till the end, save the result as a .dtsx package.
• open the package in Visual Studio with SQL Server Data Tools
• modify the package up to your needs (for example logically regrouping the tables in different DFTs, adding any additional transformations).

b) Manually edit the package code (some BIML knowledge might be needed):

• In Visual Studio with SQL Server Data Tools, create 1 DFT which will be your sample.
• In Solution Exporer, right-click on your package, select View Code.
• Either copy/paste the DFT 50+ times, changing the table names, or maybe you will even manage to automate your BIML somehow to avoid copy/paste