SSIS - ForEach From Variable Enumerator

Question

Post reply

SSIS - ForEach From Variable Enumerator

Prasad Boppana

Say Hey Kid

Points: 687
More actions
December 21, 2009 at 4:25 pm

#140227

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

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

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

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 1

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)

Prasad Boppana Say Hey Kid Points: 687 More actions · Answer 2

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,

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 4

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 5

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 6

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 7

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]

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

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

Prasad Boppana Say Hey Kid Points: 687 More actions · Answer 9

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.

Prasad Boppana Say Hey Kid Points: 687 More actions · Answer 10

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

Thanks,

Prasad

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 11

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 13

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)

Phil Parkin SSC Guru Points: 246944 More actions · Answer 14

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin