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.