Using Derived Column Transformation

Add a derived column with the following expression

(DT_WSTR,255)[ColumnB]

When if fails you can use the Error Output to check the bad values causing the exception

Using Data Conversion Transformation

You can also achieve this using a Data Conversion transformation component. Just select the ColumnB as input and choose to convert to DT_WSTR data type with length = 255

Using Script Component

You just have to select ColumnB as Input column, add an Output column outColumnB of type DT_WSTR and length = 255. And just assign the input column to the output column inside the script.

Row.outColumnB = Row.ColumnB

Update 1 - Excel data types

Based on the following official documentation:

• Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)
  The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). SSIS maps the Excel data types as follows:

• Numeric - double-precision float (DT_R8)

• Currency - currency (DT_CY)

• Boolean - Boolean (DT_BOOL)

• Date/time - datetime (DT_DATE)

• String - Unicode string, length 255 (DT_WSTR)

• Memo - Unicode text stream (DT_NTEXT)

Use data conversion tool and convert DT_WSTR to DT_STR.Check this:

• Import Excel unicode data with SQL Server Integration Services

DT_TEXT and DT_STR from input are non-unicode and excel inputs are all unicode so your conversion is not unicode to non-unicode but non-unicode to unicode.

All your Data Conversion Output Columns (excel input) should be DT_NTEXT and DT_WSTR then.