Can I read from multiple table at run time

Hi,

My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.

Right now I have different Source adapters for each table. I want to reduce the number of source adapters.

Can I

1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).

Issues

a) Most of the table has same schema, but few have different, so all tables which has same schema will work.

For different schema anyway I have to create different data source.

b) Will Destination column mapping work.

If any one has already worked on it let me know.

Thanks

Dharmbir

Hi Dharmbir,

I think you are on the right track and I have succeeded with a similar requirement in the past.

Your variable SQL statement will need to return a consistent list of columns back to SSIS. Eg if your first iteration through the loop ran:

select col_a, col_b from table_1

... but your second iteration was just

select col_a from table_2

... this would fail. To avoid this, rejig your second sql statement along these lines:

select col_a, 'any string' as col_b from table_2

This technique will trick SSIS into thinking it is receiving the same answer set each time. Beware of datatype mismatches.

Good luck.

Mike