SSIS : Conversion text stream DT_TEXT to DT_WSTR

Question

I am creating a package in SSIS, and want to convert a file with one large column into multiple columns.

I have a table containing several rows with a single column of raw data. The data was copied from a notepad file, and each row contains pipe delimiters to separate each column, but because it is a notepad file, each row is copied as one large column. I want to convert each column per row to multiple columns based on their start/end positions.

I tried using SSIS Derived Column Transformation with the SUBSTRING function, but the Data Type is automatically populated as text stream[DT_TEXT], and I get the following error:

Error at [Derived Column[113]]; The function “SUBSTRING” does not support the data type “DT_TEXT” for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error at [Derived Column[113]]; Evaluating function 'SUBSTRING' failed with error code 0xC0047089.

Error at [Derived Column[113]]; Computing the expression "SUBSTRING[RawData],1,5)" failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at [Derived Column[113]]; The expression "SUBSTRING[RawData], 1,5)" on "Derived Column.Outputs[Derived Column Output].Coluns[Derived Column 1] is not valid

Error at [Derived Column[113]]; Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]. "

When I review other Derived Column Transformation illustrations utilizing SUBSTRING with a file containing individual columns, I notice the Data Type is shown as DT_WSTR.

Do I need to convert to this Data Type? If so, how do I explicitly cast DT_TEXT data types to DT_WSTR with a cast operator in SSIS Derived Column Transformation?

Otherwise, how else could I handle this conversion?

Derived Column Name: EmployerNo
Derived Column:      Replace 'RawData'
Expression:          SUBSTRING( [RawData], 1, 5 )
Data Type:           text stream[DT_TEXT]

I expect the RawData column to be split up (converted) into 8 different columns based on their start and end positions.

Answer 1

Refering to SUBSTRING (SSIS Expression) documentation:

Returns the part of a character expression that starts at the specified position and has the specified length.

You have to convert DT_TEXT column to DT_STR/DT_WSTR before using Substring() function, you can do this using a Script Component, you can use a similar function:

string BlobColumnToString(BlobColumn blobColumn)
{
    if (blobColumn.IsNull)
        return string.Empty;

    var blobLength = Convert.ToInt32(blobColumn.Length);
    var blobData = blobColumn.GetBlobData(0, blobLength);
    var stringData = Encoding.Unicode.GetString(blobData);

    return stringData;
}

Or if the DT_TEXT length doesn't exceed the DT_STR length limit try using the following SSIS expression:

SUBSTRING( (DT_STR,1252,4000)[RawData], 1, 5 )