SSIS - ForEach From Variable Enumerator

  • Hi,

    I'm using ForEach Loop container and selected "ForEach From Variable Enumerator" option. I created a variable of System.Object(User::Collection) and selected the "User::Collection" for Variable dropdown in Enumertaor configuration.

    I have a script task that populates the "User::Collection" variable. The code is listed below

    Public Sub Main()

    '

    ' Add your code here

    '

    Dim arr As New ArrayList

    arr.Add("D:\Tests\a.txt")

    arr.Add("D:\Tests\b.txt")

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

    Dts.TaskResult = Dts.Results.Success

    End Sub

    The script task that populates the collection is connected to the foreach loop. In the foreach loop, there is a script task that reads the indexed value and displaying the value. I'm able to read only the first value and not all the other values in the collection. Any thoughts?

    Thanks in Advance

  • In plain English, what is it that you're trying to do?

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

    I'm trying to use "ForEach From Variable" Enumerator. In the foreach loop, I'm able to read only the first item from the collection.

    How can I access all the items in the collection?

    Thanks,

  • Like Jeff, I'm not sure what it is you are trying to do.

    what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset?

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • [font="Comic Sans MS"]

    Works fine for me.

    How are you trying to access the variable from the object? Did you define another variable and mapped in for each loop?

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • [font="Comic Sans MS"]

    The only additional thing I did was to define a variable user::fileJustFound (string).

    On for each loop container -> variable mapping -> mapped user::fileJustFound as 0 (the only one variable)

    Then used a simple file system task to delete the file with name = user::fileJustFound....

    It looped through successfully and deleted files. Wondering if you are missing this part of mapping the variable appropriately.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • sabyasm (12/22/2009)


    [font="Comic Sans MS"]

    The only additional thing I did was to define a variable user::fileJustFound (string).

    On for each loop container -> variable mapping -> mapped user::fileJustFound as 0 (the only one variable)

    Then used a simple file system task to delete the file with name = user::fileJustFound....

    It looped through successfully and deleted files. Wondering if you are missing this part of mapping the variable appropriately.

    [/font]

    I would ask whats the point of using the Script Task if its a simple File directory parser, why not simply use the built in file Enumerator thats provided in the Task.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset?

    [font="Comic Sans MS"]You already asked 😛 ...

    I was more curious and tried to help on why it wasn't working - rather than why he is using this approach..[/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • sabyasm (12/22/2009)


    what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset?

    [font="Comic Sans MS"]You already asked 😛 ...

    I was more curious and tried to help on why it wasn't working - rather than why he is using this approach..[/font]

    Touche....:-D

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason Lees-299789 (12/22/2009)


    Like Jeff, I'm not sure what it is you are trying to do.

    what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset?

    I need to get a list of Names from the database and use the list it in multiple tasks of the package. So I'm getting the names from the database and storing them in to a array list. The array list is stored in to a variable of System.Object. This will avoid the trips to the database to get the names every time I need them.

    I'm trying to figure out how to read the names from the Variable(User::Collection) using the ForEach From Variable Enumerator.

    Hope this helps.

  • That worked for me. I was able to figure out where I'm doing wrong.

    Thanks,

    Prasad

  • Firstly I wouldnt use a Script task they ar quite slow and can have problems on 64bit machines if they arnt precompiled.

    I would use an Execute SQL task to get the data set and assign it to a Variable of Type Object.

    You can then use the For Each ADO enumerator to parse the Each element of Object Variable into a New Variable using the Variable Mapping section of the Loop Column 1 in the record set = Index 0 on the mappnig (Column 2= 1, and so on).

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • [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]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • 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.

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

  • 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? 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 36 total)

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