SSIS Convert Between Unicode and Non-Unicode Error

Question

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet in the Excel workbook, the SQL is in the excel connection manager (effectively a create table statement that creates a sheet) and is derived from the mapping of the columns from the DB.

No matter what I have done I keep getting this unicode --> non-unicode conversion error between my source and destination. Tried conversion to string[DT_STR] between S > D, removed it, changed SQL Table VARCHAR to NVARCHAR and still get this flippin error.

Because I am creating the sheet in Excel with a SQL statement I do not see any way to actually pre-define what the data types of the columns will be in the Excel sheet. I imagine it would be a default meta data but I do not know.

So between my SQL table destination and the creation of my Excel sheet with this SSIS sql statement how can I stop this error coming up?

My error is:

Error at Data Flow Task [OLE DB Source [1]]: Column "MyColumn" cannot convert between unicode and non-unicode string data types.

And for all nvarchar columns.

Appreciate any help

Thanks

Andrew

Answer 1

Add Data Conversion transformations to convert string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings.

You need to do this for all the string columns...

Answer 2

Below Steps worked for me:

1). right click on source task.

2). click on "Show Advanced editor". advanced edit option for source task in ssis

3). Go to "Input and Output Properties" tab.

4). select the output column for which you are getting the error.

5). Its data type will be "String[DT_STR]".

6). Change that data type to "Unicode String[DT_WSTR]". Changing the data type to unicode string

7). save and close. Hope this helps!