Post reply

How to create multiple text files from one table

  • June 6, 2008 at 8:15 am

    #183183

    Greetings all,

    I'm having a learning curve moment.

    I have a table that holds POS data - TimePeriod, product, store, quantity sold, dollar sales etc. where time period is in weeks.

    What I'd like to do is, for each distinct value of TimePeriod in the table, I want to dump the matching rows of that table to a text file called TimePeriod(TimePeriod).txt.

    Can anyone just give me a few nudges in the right direction? I'm not even sure which of the tools are the right ones to use.

    Appreciating it,

    Joe

  • June 6, 2008 at 9:36 am

    #825548

    Joe,

    If you are using statically defined time periods, you can create a Conditional Split task and branch out to a text output for each time period. However, this could become a very cumbersome build if you have more than a few time periods, and if I read your intent correctly, you want to have the time period outputs defined at runtime.

    Your best bet for this will be the script task, which will allow you to queue up all records belonging to a time period and then write each time period out to a file.

    If you're unfamiliar with how to do this in a script, post back and I'll put some snippets together for you as an example.

    hth,

    Tim

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

  • June 6, 2008 at 9:56 pm

    #825782

    Thanks for the reply - I'll play around with the script task and see what I come up with.

  • June 8, 2008 at 8:36 am

    #825893

    Hey Joe,

    Here is an alternative that you may want to consider as well...

    If you know what your time periods are (i.e. they reside on your database on a table), why not place these into an object variable, and work with the For Each ADO enumerator. During your loop, you can specify the SQL statement to be used to export the data, as well as changing the connectionstring on your file.

    Good Luck!

    ~PD

  • June 10, 2008 at 2:01 pm

    #826958

    Sounds like fun! I'll try that too and let you know how it went.

  • June 19, 2008 at 12:49 pm

    #831328

    Can you please give an example of how you can use the Foreach loop to change the SQL on an OLE DB data source within a Data Flow task?

  • June 19, 2008 at 2:05 pm

    #831403

    I just replied to another thread that's along these same lines... see if this helps:

    http://www.sqlservercentral.com/Forums/Topic520202-148-1.aspx

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 7 posts - 1 through 6 (of 6 total)

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