Post reply

Read multiple flat files with different formats and output to SQL Server

  • August 19, 2011 at 8:55 am

    #390229

    Hi all,

    Got a question that I'm not sure if there's an actual solution for.

    I have a script which downloads reports from an external source, and then, using a third-party program, parses the reports into a standard table format and outputs them to CSV files.

    The reports are completely different from eachother - they may share a few fields in common, but if they do, it is just a coincidence.

    What I need to do is have a flat file source to read in these CSV files, and then output them to a SQL Server table.

    I can set up the flat files to have the same column headers as the database table. Is there any way I could use a FOREACH loop or something to that effect, which would have those two steps?

    I expect the answer is no, since the flat files have different meta data, but if anyone has a solution for this, I'm open to suggestions.

  • August 19, 2011 at 9:04 am

    #1370811

    unless you can make specific assumptions, there's no easy way to do this.

    if the first x number of columns are the same across all csv files, that might be a starting point.

    if the csv files all have header information, then that might be helpful as well, since you could query their metadata and build some dynamic sql on them witht he JET driver pointing to a folder they all exist in.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 24, 2011 at 9:46 am

    #1372682

    If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. The enhanced data flow task supports dynamic data flows at runtime, so you can process all your input flat files with only one Data Flow Task, executing inside For Each Loop container.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 3 posts - 1 through 2 (of 2 total)

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