ForEach Loop For Multiple CSV File Exports

Question

ForEach Loop For Multiple CSV File Exports

Robert O'Byrne

SSCrazy

Points: 2017
More actions
April 5, 2007 at 12:54 pm

#177566
I have a table with monthly records by customer. I would like to loop through this table and export each customers records to a csv file in their own directory like this: f:\CFiles\Cust01\20070331.csv, f:\CFiles\Cust02\20070331.csv, etc.
I’ve already successfully set up the following steps in SSIS:
1. Created a script task to set up the path and date variables
2. Created a SQL task executing a stored procedure to group the monthly table by customer and place the results in a results set variable.
3. Created a for/each loop based on the above results set variable to loop through each customer.
The above steps are working properly. Now I want to execute a stored procedure selecting all records from my table where the customer = the for/each enumerator value. I then want to export the data into individual csv files.

How do I complete the exporting phase? I’ve tried placing a data flow in the for/each container, but I can’t seem to link the enumerator with the oledb source in the data flow.
Am I missing something, or not using the correct task?
Any suggestions are greatly appreciated!

Thanks!
-r.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 1

I had to do a similar thing but creating individual files for each order, using the order number as the filename. I will explain how I did this so it may help but let me know if you need any further assistance or translation so it makes sense for customers.

How I did it:

1. Define a global variable of type Object called Orders, a int32 variable called OrderID and a variable called Dynamic Filename as type string

2. Create an Exec SQL task that returned a Full record set which populated a variable of type object I had defined called Orders

3. The source for my For Each Loop of type Foreach ADO Enumerator was The result set obtained in step 2 called Orders. From here I could map the OrderID and DynamicFilename properties in the variable tab to the corresponding columns of my orders object.

4. Inside my For Each loop I have a series of statements commencing with a OLE DB Source. This runs a select with the following as its where clause: where (tblOrder.OrderID = ?).

5. Click the Parameters button to map the ? in the where statement above to one of your variables - so mine maps to OrderID

6. I then have a Flat File detsination that has its name created dynamically based on the OrderID and some other info I define and pass through in my original SQL select statement at stage 2. I do this using the expressions option and mapping it to @[User:ynamicFileName]. You could use this to build the path string using the customerID and the date.

Hope that helps,

Catherine

Kindest Regards,

Catherine Eibner
cybner.com.au

Robert O'Byrne SSCrazy Points: 2017 More actions · Answer 2

Thanks! That helps alot!

I was thinking the use of a data flow was the way to go, but this looks more direct. I'll play around and let you know how I make out!

Thanks for the direction!

-r.

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 3

No Worries! Glad to have been able to help!

Kindest Regards,

Catherine Eibner
cybner.com.au