Post reply

How to loop through different tables with a foreach task

  • July 7, 2010 at 8:52 am

    #224558

    Hi,

    I am french so please excuse me for my poor english level ! I have some troubles using the SSIS foreach loop task.

    I've got a table containing, in each row, a table name and some other parameters, and I want to copy the rows of these tables from one SQL Server to another SQL server.

    So I created a first SQL statement task which selects all of the table names and puts them into a variable.

    After that, I added a foreach loop, which loops on the different table names.

    This works fine, so no problem.

    The problem comes now : I add a data flow task in the foreach loop.

    The source has a connection to the first server, and uses the variable filled by the foreach loop to set the table name. Same thing for the destination : connection to the second server, and variable to set the destination table name (which is the same as the source).

    But this doesn't work, i've got plenty of errors regarding to columns which aren't available, or columns which aren't use anymore, etc...

    I don't know if it's clear enough, but is someone able to help me ?

    Thank you

    --

    V. Thomas

    France

  • July 8, 2010 at 8:28 am

    #1190281

    You can't dynamically assign tables to a data flow. SSIS will mess up the metadata, which results in the errors that you saw. A data flow has to be static in SSIS, unless you use a commercial component (like Cozyroc's dataflow+).

    You can do the following:

    * create a dataflow task for each table and dynamically assign during run-time which data flow task has to run on which moment.

    * or do everything with dynamic T-SQL. Read your table in the for each loop, get all the columns of that table and dynamically create your select and insert statements.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 8, 2010 at 8:43 am

    #1190290

    Thank you for your answer.

    I also thought about doing it with T-SQL but the problem is : I want to copy the data to an other server, which isn't linked to the source server (due to security policies in my firm).

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply