SUBSTRING does not support the data type DT_TEXT

Other SQL Server Topics
1

The data type of a source changed from varchar(255) to varchar(max). In my SSIS package, a derived column is failing because "SUBSTRING does not support the data type DT_TEXT".
This doesn't make sense. Why does it now think that the field is DT_TEXT? What is the best way to get around this, Convert, Cast?

My derived columns is using this field with SUBSTRING to create a new field called CodeNew, with data type DT_STR, length 7.
The fields MinCode and MaxCode are the two fields with the new data type varchar(max).
Derived Column:
CodeNew

(DT_STR,7,1252)(ISNULL(qInd) ? "Unknown" : SUBSTRING(MinCode,1,4) == "MISC" ? SUBSTRING(MinCode,5,4) : SUBSTRING(MinCode,5,3) + SUBSTRING(MaxCode,5,2))

2

I know nothing about SSIS, but one option might be to have SQL create the value for [CodeNew], and supply that as an additional, separate, column to SSIS.

I'm guessing that the unlimited-size of varchar(MAX) is giving SSIS some grief, whereas in a raw-SQL expression

CASE WHEN qInd IS NULL 
            THEN 'Unknown' 
        WHEN SUBSTRING(MinCode,1,4) = 'MISC' 
            THEN  SUBSTRING(MinCode,5,4)
        ELSE  SUBSTRING(MinCode,5,3) + SUBSTRING(MaxCode,5,2))
        END

would be fine, regardless of varchar(MAX) etc.

1 Like
3

What I suspect is that the DT_STR in SSIS is limited to 8000 chars, and so the VARCHAR(MAX) gets converted to DT_TEXT (which does not have that limitation). Given that, if you are able to do so @Kristen's suggestion might be the way to go. If you have fewer than 8,000 characters, you could change to varchar(8000), or cast to DT_STR as in SUBSTRING((DT_STR,4000)MinCode,1,8000).

There is a connect issue here which Microsoft has closed as being "by design"

1 Like