Help SSIS For each loop with multiple tables

My client has a NAV database with multiple companies. Each company has its own set of tables. Tables are prefixed by a company name:

[Texas$G_L_ entry]

[Alabama$G_L_ entry]

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.

more ▼

asked Sep 21, 2012 at 03:46 PM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi ,

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

more ▼

answered Sep 25, 2012 at 06:03 AM

avatar image

2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 21, 2012 at 03:46 PM

Seen: 1776 times

Last Updated: Sep 25, 2012 at 06:03 AM

Copyright 2018 Redgate Software. Privacy Policy