SSIS - ForEach From Variable Enumerator

  • Jeff Moden (12/22/2009)


    Heh... I probably wouldn't use SSIS for such a thing. I damned sure wouldn't use a script for this. T-SQL would do the job just fine.

    Hi Jeff,

    I'm new to SSIS and I'm trying to see how I can use the foreach loop container in big scheme of things. This is a part of the package and package does have multiple data flow tasks in it besides the foreach loop container.

    Thanks,

    Prasad

  • sabyasm (12/22/2009)


    [font="Comic Sans MS"]

    ^^ Correct.

    Also you wouldn't want to hardcode something like database names in the script task -

    as new databases may be added or dropped.. 😎

    [/font]

    Hi Sabyasm,

    Good point. I'm not retreving and hardcoding the database names. I'm just gettng the names of the feeds we do run at work and those are configured by our team 🙂

    Thanks,

    Prasad

  • - Set up a variable to hold the Array. This must be a System.Object type variable.

    - Set up a variable to hold the current array value. The must be a String.

    - Set the array up like this;

    Dim sourceTabs() As String

    sourceTabs = "Sheet1$,Sheet2$".Split(",")

    - Set the loop up using the above variables

    - Voila!

    (The example above was used to iterate through a fixed list of Excel tabs which were then used as data sources.)

    The crucial part was to set up the object as an Array, rather than an ArrayList.

    Stu

  • Phil Parkin (12/23/2009)


    Jeff Moden (12/22/2009)


    Heh... I probably wouldn't use SSIS for such a thing. I damned sure wouldn't use a script for this. T-SQL would do the job just fine.

    So ... what would you use SSIS for Jeff? 🙂

    Heh... (counting my lucky stars as I speak)... "nothing" is all I can come up with, so far.

    --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)

  • Use ADO instead.

  • willtwc (12/22/2010)


    Use ADO instead.

    Why?:blink:

    --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)

  • Hi,

    Try checking out this article:

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/

    its a little cryptic but you have actually use the ADO Enumerator to do what you are trying to accomplish.

  • Working in 2008 R2: I attempted this setup, excpet in my case I was creating an array of strings which drive a query to read data from a database to build a new dataset. My problem is that I pass 4 values into the array and start the routine by the For Each Loop processes the 4 iterations and then keeps on goign to a fifth iteration with a vlaue of 0. Why?

  • Sounds like you are ignoring the zeroeth element of the array. Does your code for populating the array look something like this?:

    Dim MyArray(4)

    MyArray(1) = "One"

    MyArray(2) = "Two"

    MyArray(3) = "Three"

    MyArray(4) = "Four"

    So, you also have the MyArray(0) value...

  • I have tried two variations, but both give the same problem:

    Dim arr As New ArrayList

    arr.Add("636")

    arr.Add("437")

    arr.Add("618")

    arr.Add("568")

    Dts.Variables("User::VStations").Value = arr

    and

    Dim arr As Array

    arr = "636, 437, 618, 568".Split(",")

    Dts.Variables("User::VStations").Value = arr

  • Referring back to my prior post:

    "The crucial part was to set up the object as an Array, rather than an ArrayList."

    Can't remember back why this was...

    Stu

  • Try this:

    Dim arr() As String

    arr = "636, 437, 618, 568".Split(",")

    Dts.Variables("User::VStations").Value = arr

    Stu

  • I saw that reference to the Array over ArrayList and so I changed to the second instance, and this also has the same problem. It iterates through the values I give it and then doesn't stop, but goes on to do one with a 0 value. I have seen posts where I could cause a Fail to get out of the loop, but why fail when it is clear that it did not, but just never stop.

  • No better. Still goes into that last iteration.

  • Have you tried debugging the size of the array?

    What is the data type of this? (Object?)

    Dts.Variables("User::VStations").

Viewing 15 posts - 16 through 30 (of 36 total)

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