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

6
votes

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)
2
sql-serverssissql-server-2012type-conversion
Have you tried performing the conversion in a script transformation? – Tab Alleman
No... If I am not able to find a solution with regular conversions and transformations, I will work on Script Task. – Vijred

2 Answers

7
votes

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: Truncating8000PlusCharsInSSIS

4
votes

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)