My client has a NAV database with multiple companies. Each company has its own set of tables. Tables are prefixed by a company name:
there is a "global" table in the database with all the company names listed.
Currently every company's data has a SSIS package of its own, making maintenance difficult, leaves room for error and not elegant
I would like to use the SSIS "For Each Loop" task to loop over the Company Names in the Company table and then query certain tables from the database and combine the data into a single table in the datawarehouse.Is it possible? I think so, but I am struggling with it. A detailed example would be great.
first you need a execute sql task to query the table store the list of table names in an object variable.
Then use the for each loop to iterate through each table name.
Configure the for each loop to store the table name in a variable and build you connection string using an expression and the table name variable.
your DFT will be in the foreach loop , by which you could perform the same tranformation for different tables.I assume all the tables have similar structure and business rules.
Hope this helps.Thanks, Kannan
answered Sep 25 '12 at 06:03 AM