Search code examples
sql-serverexcelssisetlssis-2012

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

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?

Solution

  • 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)