Problem with Redirecting Errors and TEXT/NTEXT Columns in Flat File


  • I noticed contents in TEXT/NTEXT columns from error rows being added to the data in columns in the next good record.  Best example of this is to have a flat file with columns declared as COL 0, int, COL 1 text and COL 2, varchar(10).  The contents are

    1, Test this one, Passed

    A, Test this two, Passed

    B, Test this three, Passed

    2, Test this four, Passed

    3, Test this five, Passed

    The two rows with A and B in COL 0 will cause an invalid datatype error and be sent to the error table/file via redirection. When the next row with 2 in COL 0 goes to the destination, the COL 1 text field will be "Test this two Test this three Test this four" instead if  "Test this four". This is a simple example and everyone said use varchar/nvarchar, but I have  strings that are over 30,000 in length. What is an alternative to this problem?

    • Edited by RDWILL657 Friday, August 01, 2014 7:07 PM
    Friday, August 01, 2014 7:00 PM

All replies

  • Did not try this but looks odd.

    It seems that you may not even need the text itself captured in the error file, just the IDs.

    But I suspect you just do not see a clear picture.

    How did you determine the text spreads?

    What did you use to inspect the error file? Or may be you misconfigured the flat file destination.

    Please use the Data Viewer placed at the precedence constrained leading to the error output and inspect the records in it.

    Arthur My Blog

    Friday, August 01, 2014 7:29 PM