SSIS - Converting DT_TEXT(Length 11,000 Characters) to DT_STR and trim to 1,000 characters

Question

I want to read data from text file (.csv), truncate one of the column to 1000 characters and push into SQL table using SSIS Package.

The input (DT_TEXT) is of length 11,000 characters but my Challenge is ...

• SSIS can convert to (DT_STR) only if Max length is 8,000 characters.
• String operations cannot be performed on Stream (DT_TEXT data type)

Solution 1

Got a workaround/solution now; I truncate the text in Flat File Source and selected the option to Ignore the Error;

Please share if you find a better solution!

FYI:

Solution 2

To help anyone else that finds this, I applied a similar concept more generally in a data flow when consuming a text stream [DT_TEXT] in a Derived Column Transformation task to transform it to [DT_WSTR] type to my defined length. This more easily calls out the conversion taking place.

Expression: (DT_WSTR,1000)(DT_STR,1000,1252)myLargeTextColumn
Data Type: Unicode string [DT_WSTR]
Length: 1000

*I used 1252 codepage since my DT_TEXT is UTF-8 encoded.

For this Derived Column, I also set the TruncationRowDisposition to RD_IgnmoreFailure in the Advanced Editor (or can be done in the Configure Error Output, setting Truncation to "Ignore failure")

(I'd post images but apparently I need to boost my rep)