SQL Server Performance

SSIS doesn't see the row delimiter in a text file import

Discussion in 'SQL Server 2005 Integration Services' started by WingSzeto, Nov 5, 2008.

  1. WingSzeto Member

    We have a text file import which use 'tab' as a column delimiter and <>CR> <LF> as a row terminator. It is working under DTS in SQL 2000. Once we upgrade it to SQL 2005, I recreate the package in the new SQL integration service.
    THe text file has 30 columns. During the creation of the Data Flow Destination, I specify the Flat File connection Manager and set up the row delimiter and column delimiter in the 'Columns' settings. Here is what I see in the preview section in the 'Flat File Connection Manager Editor' in SSIS. Say we only have four records in the file. Say the first record only has the data in first 10 columns and the rest of the columns is blank. The second record has the data in its first 13 columns. The third record has data in first 20 column and the last record has 11 columns. The preview window will show the first three records become 1 record and all 30 columns in the preview window are filled with data and the rest of 13 columns from the third record is gone (43 total columns from the three records). The forth record from the text file starts the second record in the preview window.
    Even I changed the row delimiter to say '|' in SSIS and the text file, the outcome is still the same.
    I am puzzled. Again, the file format works in SQL 2000 DTS.
    Any pointers on this is very much appreciated.
    wingman
  2. martins New Member

    Check the column delimiters for each column under the "Advanced" tab, and ensure that only the last column's delimiter is {CR}{LF} and all the rest have {TAB}. Hopefully this will sort out the problem.
  3. WingSzeto Member

    Yes, only the last column has the delimiter of {CR}{LF} and the rest have {tab}.
  4. tononj New Member

    I have the same problem with a text file I am trying to use as a source. I have discovered that the file reads correctly if the rows include the same number of field delimiters. Unfortunately if not all the fields are included in a row it keeps looking for the next delimiter and ignores the row delimiter.
    I haven't found a solution yet though. It seems like a bug to me since every other application I open the file with reads it correctly.
    Joan
  5. WingSzeto Member

    I am glad that someone expereinced the same problem. Actually I agree that it is possible a gltich/bug in SSIS. How can I report this to the Microsoft as a potential bug?
    My workaround to this problem is I use Microsoft Access to read in the data from the text file and then use as the input source for SSIS. You know, it is kind of strange that MS Access can read the text file correctly but not SSIS.
    wingman
  6. satya Moderator

  7. WingSzeto Member

    I am on SQL 2005 sp2 and with hot fixes. The build number is 9.0.3233.
    wingman

Share This Page