SSIS package SQL DB to a Excel Spreadsheet destination Unicode Error

Question

I have a DB OLE Source going to an excel destination. I receive the following error

Error at Data Flow [Excel Destination [88]]: Column "X" cannot convert between unicode and non-unicode string data types.

I have added in a data conversion to change string columns to Unicode. this has not resolved the problem. any guidance would be appreciated

You can see on your destination what type your column is under mapping. I would cast your source to the appopiate datatype. Eg. select cast(ColumName as nvarchar(50) as ColumnName from XXX.XXX - If it says error then i would delete your task and create again and write your paste your correct SQL inside your statement (It doesnt handle metadata well, so you need to update it by either deleting task or go to advanced options) this works fine for me. — SqlKindaGuy
Check the solution below. Add an alias of column in data conversion and map it in excel. — Prabhat G
Can you post screenshot of your ssis solution? And screenshot from source and destination setup? — SqlKindaGuy

Prabhat G Prabhat G · Accepted Answer · 2017-10-16T09:52:52

Go to your excel destination component --> mapping --> hover your mouse over column in question, you'll see that it is Unicode Str. Something like this :

Hence, you need a data conversion component to add an alias of source column to DT_WSTR Unicode String AND map it in excel destination component.

I replicated your problem and thus providing you solution.

IF this doesn't work, then delete these components and re-add them, as this is will mostly resolve your issue.

SSIS package SQL DB to a Excel Spreadsheet destination Unicode Error

2 Answers