Post reply

SSIS 2012 DT_TEXT column truncated in Flat File Source

  • July 8, 2014 at 9:44 am

    #287787

    I'm having an issue in SSIS 2012 where a DT_TEXT column in a Flat File Source is being truncated at exactly 8MB (8,388,608 bytes). To investigate, I created a Flat File Connection Manager with only one column, DT_TEXT type, rows terminated by CR/LF. The source file for that connection manager had two data rows terminated by CR/LF. The data length in the first row exceeded 8MB, the other was just a short row. I created a data flow task with a flat file source using that connection manager and fed its output to a flat file destination also defined with a single DT_TEXT column. I ran the job and the destination file had only the first 8MB of data from the first row, followed by CR/LF. The second row did not make it into the destination file. The Execution Results showed no errors.

    I trimmed the data in the first row of the source file to exactly 8,388,608 characters, and reran. The entire first row made it into the destination file as expected, but the second row still did not make it in. I trimmed the first row to 1 less character, and again the second row did not make it into the destination file. I trimmed the first row again by 1 character (now at 8,388,606 characters) and this time the second row made it into the destination file.

    I suspected there would also be a similar truncation issue at 8MB for a Flat File Destination. So I added a Derived Column between the Source and Destination in the data flow task, in which I appended the input to itself, making it twice as long. That entire derived column made it into the destination file, so no problem on the destination side.

    I don't see anything I can change in the Flat File Connection Manager for the input file that would solve this issue. I believe I should be able to load in source rows with column data much longer than 8MB, am I mistaken?

    Thanks,

    Terry

  • February 4, 2020 at 12:56 pm

    #3722899

    Hello Everyone

    I know this is an old post - but  I am facing the same issue - DT_TEXT truncates data after 8388608 characters whereas it should  hold upto  2,147,483,647 characters. I can try other workarounds but i am very curious of what I am missing - please let me know if anyone has the answers

    Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply