[unicode] SSIS Convert Between Unicode and Non-Unicode Error

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

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...

1. First, add a data conversion block into your data flow diagram.

2. Open the data conversion block and tick the column for which the error is showing. Below change its data type to unicode string(DT_WSTR) or whatever datatype is expected and save.

3. Go to the destination block. Go to mapping in it and map the newly created element to its corresponding address and save.

4. Right click your project in the solution explorer.select properties. Select configuration properties and select debugging in it. In this, set the Run64BitRunTime option to false (as excel does not handle the 64 bit application very well).

When creating table in SQL Server make your table columns NVARCHAR instead of VARCHAR.

Instead of adding an earlier suggested Data Conversion you can cast the nvarchar column to a varchar column. This prevents you from having an unnecessary step and has a higher performance then the alternative.

In the select of your SQL statement replace date with CAST(date AS varchar([size])). For some reason this does not yet change the output data type. To do this do the following:

1. Right click your OLE DB Source step and open the advanced editor.
2. Go to Input and Output Properties
3. Select Output Columns
4. Select your column
5. Under Data Type Properties change DataType to string [DT_STR]
6. Change Length to the length you specified in your CAST statement

After doing this your source data will be output as a varchar and your error will disappear.

Source

On the above example I kept losing the values, I think that delaying the Validation will allow the new data types to be saved as part of the meta data.

On the connection Manager for 'Excel Connection Manager' set the Delay Validation to False from the Properties.

Then on the data flow Destination task for Excel set the ValidationExternalMetaData to False, again from the properties.

This will now allow you to right click on the Excel Destination Task and go to Advanced Editor for Excel Destination --> far right tab - Input and Output Properties. In the External Columns folder section you will be able to now change the Data Types and Length values of the problematic columns and this can now be saved.

Good Luck!

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!

I experienced this condition when I had installed Oracle version 12 client 32 bit client connected to an Oracle 12 Server running on windows. Although both of Oracle-source and SqlServer-destination are NOT Unicode, I kept getting this message, as if the oracle columns were Unicode. I solved the problem inserting a data conversion box, and selecting type DT-STR (not unicode) for varchar2 fields and DT-WSTR (unicode) for numeric fields, then I've dropped the 'COPY OF' from the output field name. Note that I kept getting the error because I had connected the source box arrow with the conversion box BEFORE setting the convertion types. So I had to switch source box and this cleaned all the errors in the destination box.

I have been having the same issue and tried everything written here but it was still giving me the same error. Turned out to be NULL value in the column which I was trying to convert.

Removing the NULL value solved my issue.

Cheers, Ahmed

The missing piece here is Data Conversion object. It should be in between OLE DB Source and Destination object.