locked
Truncation errors when trying to import from flat file RRS feed

  • Question

  • I am trying to update from a fixed width text file. Using ragged right since the rows are crlf delimited. The last column is fixed width 8. However I have to specify an output width of 50 for that row or I will get truncation errors about halfway through the file. I have checked the row in the file where it errors and there is nothing unusual. The last column is 8 chars just like all the rest of them. However, ssis will not import that row unless the destination table has a width of 50 for that column. I don't want a width of 50. I want 8. There is nothing over 8 in that column on the text file.
    Thursday, June 21, 2012 3:41 PM

All replies

  • I would not use the ragged right.

    Define the columns based on their length.


    Arthur My Blog

    Thursday, June 21, 2012 3:53 PM
  • Messages
    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "LastUpdatedDate" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
     (SQL Server Import and Export Wizard)
     
    Error 0xc020902a: Data Flow Task 1: The "output column "LastUpdatedDate" (70)" failed because truncation occurred, and the truncation row disposition on "output column "LastUpdatedDate" (70)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "N:\NMFTA SCAC\NMF 101-AA Effective 5-1-2012\NmftaScacImportTemp.txt" on data row 12443.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - NmftaScacImportTemp_txt" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
    Thursday, June 21, 2012 4:00 PM
  • Can't do that. If I do that it leaves the cr-lf chars in the last column. that is bad
    Thursday, June 21, 2012 4:03 PM
  • use CRLF as the delimiter

    Arthur My Blog

    Thursday, June 21, 2012 4:03 PM
  • Huh? That's what I was doing in the first place with jagged right. You can't use delimeter with fixed width.
    Thursday, June 21, 2012 4:05 PM
  • Flat file connection manager editor General

    Arthur My Blog

    Thursday, June 21, 2012 4:11 PM
  • I tried that it doesn't work. That is only specifying the header row delimiter. It leaves the cr lf at the end of every last column.
    Thursday, June 21, 2012 4:20 PM
  • How many rows in your test file.

    Please check the 12443 row according to this information -

    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "N:\NMFTA SCAC\NMF 101-AA Effective 5-1-2012\NmftaScacImportTemp.txt" on data row 12443.

    I suggest you can make a test, first time just load 1 header row and 1 data row.

    Then, if success test 10 row, 100 row.

    Sometimes, the flat file will contain some error data in columns, they're unsee extral blanks and chars. Try to redirect them to error output file for manully check.

    Just some suggestion, hope it can help you.


    Please vote if it's helpful and mark it as an answer!

    Thursday, June 21, 2012 4:28 PM
  • That row number changes randomly everytime i run it. It always successfully inserts the first 9588 rows into the table. I removed row 9589. I mixed up the order of the rows. I made the second 9588 rows equal to the first to see if it would insert them as a test and it always stops at 9588.
    Thursday, June 21, 2012 4:31 PM
  • Can you make a test to redirect the error row to a test file and check it.

    What's your last column, the data format in file and the data type in destination table ?


    Please vote if it's helpful and mark it as an answer!

    Thursday, June 21, 2012 4:44 PM
  • Another thread has the same issue, you can refer to - http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8c9c1d45-82d5-43bf-961b-a8e22dab221b


    Please vote if it's helpful and mark it as an answer!

    Thursday, June 21, 2012 4:45 PM
  • I don't understand what you are asking me to do, so no i can't, sorry
    Thursday, June 21, 2012 4:53 PM
  • I give up. Which is the usual result when I come here. I will just write custom code to import because this import wizard is garbage. It's days like this I would rather be a garbage man than a .net developer. I hate you M$, I really really do!
    Thursday, June 21, 2012 5:19 PM
  • The fact you see this truncation error is b/c the row delimiters are not set, it simply swallows the whole file as one row.

    I need to also understand better what you are doing. Perhaps let's rewind to the discovery phase. Any examples of the file can be shown?

    PS: I would refrain from public comments of this magnitude, this is my advice, I am sure you will look back at this issue an laugh, but by then you will make many people unhappy.


    Arthur My Blog

    Thursday, June 21, 2012 5:34 PM