SSIS Template for importing multiple tables

Question

Being an SSIS newbie, I am trying to figure out the best possible way to transfer multiple tables. I am trying to import multiple tables from one database to another. I could write multiple parallel data flows for each table, however, I want to be smart about it.

For each of the tables, If I were to generalize,

1. I need to transfer rows from one table to a table in another database
2. I need to count the number of rows transferred
3. Have to record the start and finish time of the data transfer for each table
4. Record any errors

I am trying not to use Stored procedures since I want people to not have to dig deep into the DB to get the rules for this transformation. I would ideally like to have this done at the SSIS level using the components that therefore can be seen visually and understood.

Any best practises that people have used before?

I would ideally want to do something like

foreach (table in list of tables to transfer) transfer (table name)

Answer 1

To make a generic table handler you would have to programatically construct the data flow. AFAIK SSIS has no auto-introspection facility. A script task will allow you to do this, and you can get the table metadata from the source. However, you will have to programatically construct the data flow, which means fiddling with the API.

I have worked on a product where this was done, although I didn't develop that component, so I can't offer words of wisdom off the top of my head as to how to do it. However, you can find resources on the web that explain how to do it.

You can find the table structure and types of the columns by querying against the system data dictionary. See this posting for some links to resources describing how this, including a link to a code sample.

Answer 2

What is your destination database doing with this info? Is it simply reading it? Perhaps you would be best served by replicating the tables.

You could create a config table that has a list of your tables you want to move and then use a for loop to do something repeatedly....but what to do.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

Below the bullet points, he states that SSIS cannot be modified to change metadata at run time. And to make it easy to maintain....you're going the wrong direction.

I'd keep it simple and use the wizard and then customize with logging/notifications etc.