none
ssis row delimiter for flat files

    Question

  • Hi,

    I was given a flat file where the first record is supposed to have column names.But since the number of columns are more the record went beyond one line for headers. Now the header which has column names went till 4 records.

    Can some one tell me how do i handle it.

    Friday, December 11, 2009 8:51 PM

Answers

  • I'm pretty sure that the stock Flat File Source won't do it for you then.  Perhaps one of those others will.

    Aside from that, I would suggest pre-processing the flat file to strip out every CR/LF except for every third one, so that you can repair the file to make one row per line.  If you did that, then the Flat File Source would work.


    Todd McDermid's Blog
    Friday, December 11, 2009 11:39 PM
    Moderator

All replies

  • are you saying that you are having trouble identifying the end of a row, or are you saying that the headers row has more/less values in it than the actual data rows, or are you saying that your file has an changing number of columns per row?
    or... are you saying something else?

    perhaps an example of you file layout would help.
    Friday, December 11, 2009 10:36 PM
  • Yes, I am having trouble identifying the end of the rows. The column numbers are fixed. The Input file is comma delimited


    Col1,Col2,col3,col4.........col26          -- end of line 1 
    Col50,Col51...................Col46         --end of line 2
    Col47,Col48...................Col68         --end of line 3        /* column headers are till here */ 
    data,data,...............                        --end of line 4       /* data for 1st record*/
    data,data.................                       --end of line 5      /*data for 1st record */
    data,data............                            --end of line 6      /* data for 2nd record */
    data,data............                            --end of line 7      /* data for 2nd record */
    data,data............                            --end of line 8      /* data for 2nd record */


    How do i identify column headers and its respective records?? Here the starting point of the records may change depending on the data in records.

    Should I put any Row delimiter to identity the records and the headers??
    Friday, December 11, 2009 10:51 PM
  • The SSIS Flat File Connection Manager and Source don't handle irregular column counts.

    Read up on John Welch's techniques for Handling Flat Files with Varying Numbers of Columns.

    Or you may want to try one of the open source text file sources that do better at handling missing columns:
    Delimited File Reader Source - A source component capable of parsing delimited flat files, including files with rows that are missing column fields.
    Delimited File Source - An extremely robust flat file source for Integration Services which handles quoted text and provides full data auditing. Based on the work of Microsoft's Bob Bojanic (Delimited File Reader Source Sample).

    Todd McDermid's Blog
    Friday, December 11, 2009 11:05 PM
    Moderator
  • The number of columns here are fixed (68). Each data record also has 68 columns delimited by ",". The thing is the data doesn't fit in one single line in a text file. There is no row delimiter. 

    Friday, December 11, 2009 11:10 PM
  • I'm pretty sure that the stock Flat File Source won't do it for you then.  Perhaps one of those others will.

    Aside from that, I would suggest pre-processing the flat file to strip out every CR/LF except for every third one, so that you can repair the file to make one row per line.  If you did that, then the Flat File Source would work.


    Todd McDermid's Blog
    Friday, December 11, 2009 11:39 PM
    Moderator
  • I'm not in a place to test this right now, but you should be able to set your row delimiter to a , by typing it in to the Row Delimiter property. Then go to the advanced page in the Flat File Connection Manager, and make sure the last column has the comma defined as the delimiter.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Saturday, December 12, 2009 4:22 PM
    Moderator