April 5, 2007 at 12:54 pm
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:
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.
April 6, 2007 at 4:13 am
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
Catherine Eibner
cybner.com.au
April 6, 2007 at 6:41 am
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.
April 9, 2007 at 6:12 pm
No Worries! Glad to have been able to help!
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy