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

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)
sql-serverssissql-server-2012type-conversion


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 NickF

Related Questions