passing variables in a Foreach loop container

  • Hi all,

    I hope this is the right forum to post, else please let me know.

    I'm not an expert with SSIS but I have to maintain and extend some packages and am stuck with following problem: I need to run a stored procedure on a table, created earlier in the package, for each "client" in that table.

    What the stored procedure does, is select informations according to the client number and output it in a text file. The stored procedure call is EXEC SP101 'LIVE','Client1' then 'Client2', ...

    The part of the SSIS package that does this is

    1. <<Execute SQL task >> that selects all the distinct clients and puts the result in a system.object

    2. <<For each container>> with link to the collection retrieved above

    1. <<Execute SQL task >> that executes the SP EXEC SP101 'LIVE',? with parameter mapping to the client id, but not sure I did it the right way.

    My problem is that, although the SP is run a correct number of times, no data are generated. It seems the clientID is never passed to the SP.

    If you need any further information, please let me know.

    Any help will be much appreciated.

    Thanks a lot

    🙂

  • In the for each loop container, you need to map the current value to a variable.

    This variable is then used in the second Execute SQL Task.

    You can put a breakpoint on the execute sql task and inspect the value of the variable before it is executed.

    If the parameter mapping with the question mark is troublesome, you could create another variable, SQLQuery, and put an expression on that variable.

    For example:

    "EXEC SP101 'LIVE','" + @[User::myVariable] + "' "

    In the Execute SQL Task, you use this variable as the source for the SQL statement.

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

  • Thanks Koen, I'll give it a try and let you know ...

  • So, I got a bit further ...

    1. using the Watch, I can confirm that the variable holds the correct clientID at each loop

    2. tried to replace the "?" with @[User::clientID]

    but got following error: Execute SQL Task: Executing the query ""exec SP101 'LIVE','" + @[User::Cl..." failed with the following error: "Incorrect syntax near '+'.".

    3. last I wanted to use this variable as the source for the SQL statement in the Execute SQL Task, as you suggested, but unfortunatly I can't figure out how to do it

    Still trying to find it out, but thanks a lot if you can help me ...

  • rot-717018 (6/4/2014)


    So, I got a bit further ...

    1. using the Watch, I can confirm that the variable holds the correct clientID at each loop

    2. tried to replace the "?" with @[User::clientID]

    but got following error: Execute SQL Task: Executing the query ""exec SP101 'LIVE','" + @[User::Cl..." failed with the following error: "Incorrect syntax near '+'.".

    3. last I wanted to use this variable as the source for the SQL statement in the Execute SQL Task, as you suggested, but unfortunatly I can't figure out how to do it

    Still trying to find it out, but thanks a lot if you can help me ...

    You can't put the expression directly in the Execute SQL Task.

    You need to put it in a variable, and then use that variable in the task.

    Execute SQL Task Editor (General Page)

    --> use SQLSourceType as Variable

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

  • ok, I understood what you meant and have put the SQL query in a variable executed in the Execute DQL Task.

    Unfortunatly still the same error. It seems it does not replace @[User::ClientID] with the ID it retrieves from the loop container.

    still searching .......

  • Is the variable property EvaluateAsExpression set to true?

    Put a breakpoint on the preexecute event of the Execute SQL Task and check the value of the SQL query in the locals window.

    Last option: use profiler to see what statement is sent to the database.

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

  • Evaluateasexpression is set to true

    ran the profiler and @[User::ClientID] is not evaluated.

    I'll look back into it on tuesday.

    anyway, thanks for your help koen, I'll keep you informed ...

  • Maybe post some screenshots Tuesday, so I can see what is going on.

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

Viewing 9 posts - 1 through 8 (of 8 total)

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