InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 01:56:43 GMT20http://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxPlease refer to this article http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.htmlWed, 03 Oct 2012 08:47:25 GMTRavi Kumar Vhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxYes, VStations is an Object. I have not looked at the size of the array, no.Wed, 30 May 2012 10:16:27 GMTgeorge.allenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxHave you tried debugging the size of the array?What is the data type of this? (Object?)Dts.Variables("User::VStations").Wed, 30 May 2012 10:01:19 GMTstuart.steedmanhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxNo better. Still goes into that last iteration.Wed, 30 May 2012 09:49:36 GMTgeorge.allenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxI 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.Wed, 30 May 2012 09:27:43 GMTgeorge.allenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxTry this:Dim arr() As Stringarr = "636, 437, 618, 568".Split(",")Dts.Variables("User::VStations").Value = arr StuWed, 30 May 2012 09:26:11 GMTstuart.steedmanhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxReferring 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...StuWed, 30 May 2012 09:17:53 GMTstuart.steedmanhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxI 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 = arrand Dim arr As Array arr = "636, 437, 618, 568".Split(",") Dts.Variables("User::VStations").Value = arrWed, 30 May 2012 09:04:03 GMTgeorge.allenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxSounds 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...Wed, 30 May 2012 08:21:46 GMTstuart.steedmanhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxWorking 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?Wed, 30 May 2012 08:03:20 GMTgeorge.allenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxHi,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.Wed, 18 May 2011 13:22:39 GMTchanna wijesinghehttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]willtwc (12/22/2010)[/b][hr]Use ADO instead.[/quote]Why?:blink:Wed, 22 Dec 2010 22:27:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxUse ADO instead.Wed, 22 Dec 2010 15:56:13 GMTwilltwchttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]Phil Parkin (12/23/2009)[/b][hr][quote][b]Jeff Moden (12/22/2009)[/b][hr]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.[/quote]So ... what would you use SSIS for Jeff? :-)[/quote]Heh... (counting my lucky stars as I speak)... "nothing" is all I can come up with, so far.Wed, 22 Dec 2010 09:35:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx- 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.StuTue, 21 Dec 2010 07:32:54 GMTstuart.x.steedmanhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]sabyasm (12/22/2009)[/b][hr][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.. :cool:[/font][/quote]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,PrasadWed, 23 Dec 2009 07:35:29 GMTPrasad Boppanahttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]Jeff Moden (12/22/2009)[/b][hr]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.[/quote]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,PrasadWed, 23 Dec 2009 07:31:18 GMTPrasad Boppanahttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]Jeff Moden (12/22/2009)[/b][hr]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.[/quote]So ... what would you use SSIS for Jeff? :-)Wed, 23 Dec 2009 00:05:33 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxHeh... 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.Tue, 22 Dec 2009 20:14:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[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.. :cool:[/font]Tue, 22 Dec 2009 11:37:34 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxFirstly 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).Tue, 22 Dec 2009 10:16:21 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxThat worked for me. I was able to figure out where I'm doing wrong.Thanks,PrasadTue, 22 Dec 2009 09:37:18 GMTPrasad Boppanahttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]Jason Lees-299789 (12/22/2009)[/b][hr]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?[/quote]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.Tue, 22 Dec 2009 09:36:29 GMTPrasad Boppanahttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]sabyasm (12/22/2009)[/b][hr][quote]what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset? [/quote][font="Comic Sans MS"]You already asked :-P ...I was more curious and tried to help on why it wasn't working - rather than why he is using this approach..[/font][/quote]Touche....:-DTue, 22 Dec 2009 09:08:52 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote]what is the purpose of the package? Is it to parse through a list of files?, to loop through a dataset? [/quote][font="Comic Sans MS"]You already asked :-P ...I was more curious and tried to help on why it wasn't working - rather than why he is using this approach..[/font]Tue, 22 Dec 2009 09:00:36 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[quote][b]sabyasm (12/22/2009)[/b][hr][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][/quote]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. Tue, 22 Dec 2009 08:57:18 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[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]Tue, 22 Dec 2009 08:49:53 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspx[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]Tue, 22 Dec 2009 08:46:05 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxLike 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?Tue, 22 Dec 2009 08:38:17 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxHi,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,Tue, 22 Dec 2009 07:59:03 GMTPrasad Boppanahttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxIn plain English, what is it that you're trying to do?Mon, 21 Dec 2009 18:07:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic837644-148-1.aspxHi,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 belowPublic 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 SubThe 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 AdvanceMon, 21 Dec 2009 16:25:57 GMTPrasad Boppana