none
SSIS text file import skips some rows

    Question

  • Hi Guys,

    I have some flat files (.txt file)which are loaded into the database on a daily basis. Each file contains about 500 rows  ,The interesting thing is that the SSIS doesn't import the correct rows for some flat files, but for others it can import the rows correctly. For example, when i import a 348 rows file into my DB, the job works well with no error, but the info of  package execution progress tells that "The total number of data rows processed for the file is 285" .waht's wrong? I am so frustrated with this problem, Any tips are appreciated~
    Thursday, August 27, 2009 10:00 AM

Answers

  • Excel is more tolerant of missing delimiters than SSIS is. Also, Excel parses row delimiter first, then column delimiter, where SSIS parses by column delimiter (the row delimiter is treated as just another column). This can cause issues if you have fewer tabs in a row than SSIS expects, even if the row delimiters are set properly. There's an example of this propblem here: http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

    The solution is to either make sure your delimiters are 100% correct, or implement something along the lines of the post I referenced above.


    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Tuesday, November 17, 2009 7:56 PM

All replies

  • Hi,
    Could you check if the number of commas or the column delimiter is same in all the rows?

    If the number of delimiter is less than the required SSIS takes the next line in the same records.
    This could be one of the reasons u are facing the issue.

    Another thing to check in the data flow, in the flat file source, error configurations have u set to ignore errors?
    In this case the error records will be ignored and only the valid records will be taken.

    Another case - > are u using any kind of conditional split which is removing some records??
    Hope this helps !! Please close the threads once answered - Sudeep
    Thursday, August 27, 2009 10:05 AM
  • hi,
    Are you dumping the data to database? please check the datbase after the each execution and cross-check with input file.
    debug your package in BIDS with same file and try to check the no of rows after each transformation.

    Sudeep's point is also possible.
    please tell us more about your package implementation, what are the diffferent tasks and transformation you are using and their purpose?


    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Thursday, August 27, 2009 10:08 AM
  • Hi,
    Thanks for your replay,I  have checked that the row delimiter is "{CR}{LF}" and the column delimiter is "tab" in the flat file, and i can get  the correct rows  through using the excel (data from txt,setting the  column delimiter  "tab")  but in my SSIS package the records are less than expected.

    In the error configurations ,i use the default setting, failing the component not ignoring the error.

    I don't use any conditional split ,the SSIS package job is very simple,just importing the txt file into my DB, BTW, the txt files  are exported by other servers , and i can import some files into my DB with correct row numbers, but for some others ,it seems to ignore some rows.
    Monday, August 31, 2009 3:28 AM
  • Hi,
    Also thanks for your replay,  i  dump  the data to my DB on a daily basis and the SSIS Package job ,as referred above, is quite simple---importing the txt file into my DB (flat file source ----->oledb destination)
    Monday, August 31, 2009 3:41 AM
  • For the files that give less number of output as expected..
    do u see the last column data on some records more than usual?? or any descripancy in the last column data?
    Hope this helps !! Please close the threads once answered - Sudeep
    Monday, August 31, 2009 9:34 AM
  • I'm having the same problem.  A text file, saved from Excel as tab dellimitated, has been working for years now a  few new rows in the middle of are getting munged together.  But we cannot find the issue and have recreated the file from scratched.
    Pasting from the text file back into excel all lines up correcty.
    but in SSIS via the file connection manager those new rows are combined.

    Please let us know what your fix was, it might help.

    much appreciated!
    Tuesday, November 17, 2009 3:05 PM
  • Excel is more tolerant of missing delimiters than SSIS is. Also, Excel parses row delimiter first, then column delimiter, where SSIS parses by column delimiter (the row delimiter is treated as just another column). This can cause issues if you have fewer tabs in a row than SSIS expects, even if the row delimiters are set properly. There's an example of this propblem here: http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

    The solution is to either make sure your delimiters are 100% correct, or implement something along the lines of the post I referenced above.


    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Tuesday, November 17, 2009 7:56 PM
  • To get the workaround and Step by Step description for developing SSIS package in order to overcome the issue with SSIS while importing text files with Flat File Connection Manager and  Flat File Source where the "Row Delimiter" property does not work properly for rows having NULL or empty values, follow the below link:

    http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/

    Thanks,

    SQL Lion

    • Proposed as answer by SQL Lion Sunday, April 04, 2010 6:57 PM
    Sunday, April 04, 2010 6:56 PM
  • I encountered the same behaviour : a fixed width text file with over 3000 rows, and only 160 or so got imported. I played around with LF and CR, to no avail. In the end the solution for me was following : I had some fields that were numeric in the db, yet DT_STRING in the raw file definitions in SSIS. When there was a blank space in the file, the row would get skipped, no error, no nothing. After altering the raw file column definitions to four-byte unsigned integer, the import worked like a charm.

    All the best, Peter

    Tuesday, April 03, 2012 12:42 PM