May 16, 2007 at 2:23 pm
Folks:
I have an SSIS package I created for a customer to import .csv files, and massage the data. I used a foreach loop to accomplish this and it works just great.
Now they tell me they want me to export this data to multiple data files based on storeid and productID. So, I would have a file for storeid = 1 and productid = 1, another file for store = 1 and productid = 2, then another file for storeid = 2, and productid = 1, so forth and so on.
I'm not sure how to accomplish this?
Any help would be appreciated.
HGHumphrey
May 16, 2007 at 10:39 pm
Are you familiar with the conditional split task? This component will allow you to redirect your output to multiple paths based on criteria you specify. This should give you what you need.
hth
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 18, 2007 at 11:11 am
Thanks for the response.
The only problem with using the conditional split, is I'm creating 3000 output files from the data table. Unless I can make the conditional split dynamic, then this does me no good.
If it is possible to make it dynamic, can you provide a brief synopsis?
As of now, I have the created a T-SQL cursor to cursor through the data and bcp it out to export files. It works, but for learning sake, I'd like to figure out how to do it in SSIS.
HGHumphrey
May 22, 2007 at 2:15 pm
If your output file format is the same , or at least managable,(say 3 or 4 different formats) I think i would:
1. create a table with the dynamic variables info. (storeID, ProductID, filenames, etc)
2. import this into a local object, ADO table.
3. use a for next loop to populate the needed variables
4. output files to a working directory.
5. move/copy files with distinct filenames from variables, to a output or archive directory.
Hope this helps....
arkware
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply