SSIS - ForEach From Variable Enumerator

Question

SSIS - ForEach From Variable Enumerator

Viewing 15 posts - 16 through 30 (of 36 total)

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

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

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

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

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

stuart.x.steedman SSC Journeyman Points: 89 More actions · Answer 3

- 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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

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

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)

willtwc Ten Centuries Points: 1099 More actions · Answer 5

willtwc

Ten Centuries

Points: 1099

December 22, 2010 at 3:56 pm

#1265731

Use ADO instead.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

willtwc (12/22/2010)
Use ADO instead.

Why?:blink:

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

channa wijesinghe Old Hand Points: 337 More actions · Answer 7

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.

george.allen Old Hand Points: 327 More actions · Answer 8

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?

stuart.steedman SSC Veteran Points: 247 More actions · Answer 9

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

george.allen Old Hand Points: 327 More actions · Answer 10

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

stuart.steedman SSC Veteran Points: 247 More actions · Answer 11

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

stuart.steedman SSC Veteran Points: 247 More actions · Answer 12

Try this:

Dim arr() As String

arr = "636, 437, 618, 568".Split(",")

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

Stu

george.allen Old Hand Points: 327 More actions · Answer 13

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.

george.allen Old Hand Points: 327 More actions · Answer 14

george.allen

Old Hand

Points: 327

May 30, 2012 at 9:49 am

#1494754

No better. Still goes into that last iteration.

stuart.steedman SSC Veteran Points: 247 More actions · Answer 15

Have you tried debugging the size of the array?

What is the data type of this? (Object?)

Dts.Variables("User::VStations").