x

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

longinthetooth gravatar image

longinthetooth
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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x942
x737
x24

asked: Sep 21, 2012 at 03:46 PM

Seen: 1134 times

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