ForEach Loop For Multiple CSV File Exports
-
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:
- Created a script task to set up the path and date variables
- Created a SQL task executing a stored procedure to group the monthly table by customer and place the results in a results set variable.
- 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.
-
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 -
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.
-
No Worries! Glad to have been able to help!
Kindest Regards,Catherine Eibner
cybner.com.au
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply