How to pass to foreach loop container variables result from sql query ?

  • Would an Enumerator be required for this?

    Cheers,

    Jaybee

  • Could you explain your problem a bit more clearly?

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

  • Yes - you'll need an enumerator. Most likely - you'll need an ADO enumerator.

    Rough steps:

    1. set up a package-level variable to type object (for this we will call it ObjectVar)

    2. set up a predecessor Execute SQL task. it's the one that will query for the records you want to iterate through. Set it up as an ADO.NET type connection, and set the Result Set option to be the entire result set (default is set at none). Finally - under the Result Set tab, Add a mapping that associates Result name "0" to ObjectVar.

    3. Right-click on your ForEach container, and pick Edit. Once you do, change the ForEach type to be a ForEach ADO Enumerator, and point if to ObjectVar. By default it will cycle through each record in the result set you passed in earlier.

    4. You will need to set up a variable for each column you want to use under the ForEach "variable" mapping. Associate the variable to the columns in order (column 0 = left most column as defined in your query, etc...)

    5. and voila.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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