SSIS: String Variables in Derived Columns

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’

Screenshot of SSIS

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:

  1. we can using Derived Column ‘Advanced Editor ‘ to manually changed the new derived column type and length to correct one
  2. Assign default value to string variable, make the content is not blank.

Leave a Reply

Your email address will not be published. Required fields are marked *