In SSIS, How to convert unicode datatype to excel column?

3
votes

I am working on SSIS Package to export the output data to Excel file. ( Excel Destination ).

I am running into conversion error.

Error Description : cannot convert between Unicode and non-Unicode string data types

Input Column Details

ColumnA ([DT_TEXT])
ColumnB ([DT_STR],200)

Data Conversion Output Column Details

ColumnA ([DT_TEXT])
ColumnB ([DT_WSTR],255)

How to convert Unicode datatype to excel column?

2
sql-serverexcelssisetlssis-2012

2 Answers

3
votes

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:

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)
1
votes

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