December 21, 2009 at 4:25 pm
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
December 21, 2009 at 6:07 pm
In plain English, what is it that you're trying to do?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2009 at 7:59 am
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,
December 22, 2009 at 8:38 am
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
December 22, 2009 at 8:46 am
[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]
December 22, 2009 at 8:49 am
[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]
December 22, 2009 at 8:57 am
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
December 22, 2009 at 9:00 am
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]
December 22, 2009 at 9:08 am
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
December 22, 2009 at 9:36 am
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.
December 22, 2009 at 9:37 am
That worked for me. I was able to figure out where I'm doing wrong.
Thanks,
Prasad
December 22, 2009 at 10:16 am
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
December 22, 2009 at 11:37 am
[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]
December 22, 2009 at 8:14 pm
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
Change is inevitable... Change for the better is not.
December 23, 2009 at 12:05 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply