December 23, 2009 at 7:31 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.
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
December 23, 2009 at 7:35 am
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
December 21, 2010 at 7:32 am
- 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
December 22, 2010 at 9:35 am
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
Change is inevitable... Change for the better is not.
December 22, 2010 at 3:56 pm
Use ADO instead.
December 22, 2010 at 10:27 pm
willtwc (12/22/2010)
Use ADO instead.
Why?:blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2011 at 1:22 pm
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.
May 30, 2012 at 8:03 am
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?
May 30, 2012 at 8:21 am
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...
May 30, 2012 at 9:04 am
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
May 30, 2012 at 9:17 am
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
May 30, 2012 at 9:26 am
Try this:
Dim arr() As String
arr = "636, 437, 618, 568".Split(",")
Dts.Variables("User::VStations").Value = arr
Stu
May 30, 2012 at 9:27 am
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.
May 30, 2012 at 9:49 am
No better. Still goes into that last iteration.
May 30, 2012 at 10:01 am
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