SSIS Loading Data from Multiple Tables

Question

I am using SQL Server 2008R2. I have almost 30+ tables in database and now I need to load data from these tables into Data Warehouse.

The problem is that DFT(Data Flow Task) only allow to connect with only one table at time. To connect with other table, you need to have another DFT. Now, to extract data from 30+ tables, It seems that I need to create 30+ DFTs. Moreover! Bulk task only works for files.

So, I have two questions here:

1. How to load data from multiple tables.

2. If I create 30+ DFTs, what impact it will have on Performance.

I have seen following links:

• SSIS multiple table loads
• https://stackguides.com/questions/20093490/multiple-tables-data-extraction
• Importing data from Multiple Tables. Social MSDN

But wasn't able to get satisfactory answer. Second link says, you may create as many S2D tasks as you can but again a performance trade off.

Help is required. Thank You!

Answer 1

Please let me know whether all the tables have got same structure? Do you want to laod all these source tables into a single target table?In the dataflow task ,under SQL command,you can use a union all if all the tables have got same structure