3

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
goofyui
  • 3,362
  • 20
  • 72
  • 128

2 Answers2

3

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)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I am facing issue with this column .. ColumnA, DataType : DT_TEXT, Length : 0 – goofyui Feb 22 '19 at 15:42
  • Error: 0xC002F445 at Data Flow Task, Excel Destination [60]: An error occurred while setting up a binding for the "COLUMNA" column. The binding status was "DT_NTEXT". Error: 0xC0202025 at Data Flow Task, Excel Destination [60]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. – goofyui Feb 22 '19 at 15:57
  • I will search on that and give you a reply in a while – Hadi Feb 22 '19 at 16:00
  • This error happens, when i keep the Header only template. Template excel sheet does not have any records to it. In an assumption, if i have existing row of record. I am not facing this error – goofyui Feb 22 '19 at 16:13
  • And other problem is, every time job run .. we expect completely new set of record. At this moment, new record is appended. – goofyui Feb 22 '19 at 16:14
  • @goofyui concerning the data types check my answer update, and the link i added there are many helpful information about Excel Destination. Concerning other issues they should be posted in seperate posts with more details. – Hadi Feb 22 '19 at 23:22
1

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

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
RajnishDwivedy
  • 168
  • 1
  • 1
  • 8