Create multiple text files from data table

  • 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

  • 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

  • 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

  • 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