I was recently tried to add a string variable as new derived columns, but got a very tricky SSIS run time error:
truncation error occurred on the specified object of the specified component
Then I checked Microsoft SSIS document and found below comments about Length’
But there is not warning or error at all in in the design view. When I check the properties of derived column, I found the type is Unicode String and and Size always 0.
If adding data to a new column, the Derived Column TransformationEditor dialog box automatically evaluates the expression and sets the column length for string data. The value of this column is read-only.
Which means, by default SSIS will retrieve the data size from variable definition. The string variable content I was using always blank, that’s why size is 0 by default.
Solution
For pass this issue, I know there are two options:
- we can using Derived Column ‘Advanced Editor ‘ to manually changed the new derived column type and length to correct one
- Assign default value to string variable, make the content is not blank.