Showing posts with label container. Show all posts
Showing posts with label container. Show all posts

Sunday, February 19, 2012

How to use a Variable from the For Loop Container as a input parameter to a SP

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

MA2005 wrote:

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

The ForEach loop allows you to store the currently iterated values from the collection that you are iterating over in variables. You can then use those variables in any way that you would normally use variables in a parameterised Execute SQL Task.

I haven't gone into much detail here because I don't want to talk about stuff you already understand. Tell me what isn't clear about what I have said and I'll be glad to fill you in.

-Jamie

How to use a script to break or continue a For Each loop task?

Okay, I have a For each loop container that has an enumeration of folder locations. I run a script to filter the files in those folders and prepare them to the next task to be copy. I want the script to "continue" the loop if none of the files meet the criteria. Can someone point me into the right direction? I need some way to advance through the loop when this happens.

Thanks!

Not quite understanding your requirement but I think conditional precedence constraints will help you here: http://www.sqlis.com/default.aspx?306

-Jamie