Using SSIS ForEach Loops

Question

Using SSIS ForEach Loops

Celita

Mr or Mrs. 500

Points: 588
More actions
May 18, 2010 at 10:33 am

#222703

I have to perform the following actions:
I first run a query that gives a single column result set. For each row, the column value needs to be the input parameter to a stored procedure. That stored procedure execution gives me a bunch of OS file names as another result set. I then need to use the File System Task to copy those file names to another server.
I'm pretty sure that I need to use a ForEach Loop with a File system task inside the container, but I have no idea how to implement this, as I've never used a loop before. Does the loop type need to be a Foreach Item Enumerator? What else do I need to configure?
I have to do this for an entire year of data, and found that I should probably only do this a day at a time, because of the quantity of data involved. Other than SSIS, the only thing I can think of is a bunch of cursors to do this. Any help is GREATLY appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

There is a great example of how to use the for-each loop to loop through files, have a look at this and post back if you have any specific questions

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx"> http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Celita Mr or Mrs. 500 Points: 588 More actions · Answer 2

I don't think that link helps, as it seems to send file names in a specific location to a variable. It also shows how to configure the loops as ForEach File Enumerator, where I think I'd need an Item Enumerator (but not even really sure about that).

Say my first query results in the row values A B and C for rows 1-3 (although it is more like 1000). Then I'd have to run the following for each row, using the first as an example.

exec sp_storedproc 'A'

I would then get a bunch more results, let's call them FullFILEPATH1, FULLFILEPATH2 AND FULLFILEPATH3 (though again, there are about 50 or so). These values are not in the same directory.

Then, I'd need to copy the files returned to another location.

Then I'd have to rinse and repeat for the other rows: exec sp_storedproc 'B' ...

So, I'm thinking that I need an item enumerator to run the stored procedure, then maybe even another loop to do the copy. Am I right about this? If so how do you configure the ForEach Item Enumerator?

This has been my problem. I keep finding stuff on the ForEach File Enumerator, but not the other types.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

In order to "prime" a forEach task in SSIS:

- Create an Execute SQL Task and drag its output to point to the forEach object. Put whatever connection info and query into the ExecuteSQL task you need. SET THE RESULTSET OPTION TO "FULL RESULT SET"

- on the For each loop object: set the enumerator to be an ForEach ADO Enumerator. Create a local variable to hold the data that changes during each loop, and map that to index 0 (by the way index 0 = first column in the ADO output, 1 = second column in the output, etc....)

- in the sub-tasks, use the local variable you just defined to do whatever operations you need. As the loop ends it will increment the local variable to the next option in the For Each set.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Celita Mr or Mrs. 500 Points: 588 More actions · Answer 4

That is what I thought I needed to do - prime it. I was doing this on the Control Flow tab. I didn't think to use an ADO, as I'm still trying to figure out the differences in the types. Because of what I need to do, will I have to have a nested ForEach loop that actually captures the FULLPATHFILE results, since the original loop will be collecting IDs that the stored proc will use to get the fullpathfillename values?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

It kind of sounds that way. You may actually have a loop within a loop.

I would recommend getting comfortable with one loop for now (make sure you know how to use this). ADO is the key if you're pulling it from the DB - it took me a while to understand why my first "ForEach" wasn't looping, and switching to ADO did the trick.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Celita Mr or Mrs. 500 Points: 588 More actions · Answer 6

Thank you VERY much. The ADO is what I really needed. I think this is going to work - I just need to remind myself how to use an expression in my destination. I'll probably sort this all out in my sleep tonight. 🙂