Post reply

SSIS ForLoop Question

  • April 27, 2011 at 1:37 pm

    #237221

    * Created SSIS Package that includes a ForLoop Container.

    * I've set the following

    InitExpression - @count = 118

    EvalExpression - @Increment <= @count

    AssignExpression - @Increment = @Increment + 1

    * Package runs successfully without issue

    * I want to change the InitExpression to not be a hard coded value but to be a count from a DB table....example below.

    * select count(*) from msdb..reports where report_type = 'Errors' and report_status = 0

    * Any help anyone can provide?


    Glenn Henson

  • April 27, 2011 at 2:26 pm

    #1316897

    Use an EXEC SQL task to fill the count variable and you should be able to leave the init express alone.

    CEWII

  • April 27, 2011 at 4:33 pm

    #1316959

    Thank you for the response.

    I'm assuming you mean a SSIS SQL Task, correct?

    Should it go inside or outside the For Loop? Trying to place it outside I can't see the @count parameter.

    Inside causes other problems.


    Glenn Henson

  • April 27, 2011 at 4:41 pm

    #1316964

    Never mind, got it.....Once again Thanks for the response.


    Glenn Henson

  • April 27, 2011 at 9:23 pm

    #1317007

    hensong (4/27/2011)

    Never mind, got it.....Once again Thanks for the response.

    What did you end up doing, Glenn?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 27, 2011 at 9:38 pm

    #1317010

    As Elliott suggested I created an EXEC SQL Task to set the @count variable value.

    I had to do it outside the ForEach Loop and originally couldn't see the variable there. I dropped it and recreated with the scope as a package scope and not ForEach scope. Tested and all works as expected.


    Glenn Henson

Viewing 6 posts - 1 through 5 (of 5 total)

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