SSIS Package: convert between unicode and non-unicode string data types

Question

I am connecting to an Oracle DB and the connection works, but I get the following error for some of the columns:

Description: Column "RESOURCE_NAME" cannot convert between unicode 
and non-unicode string data types.

Value for RESOURCE_NAME:

• For Oracle: VARCHAR2(200 BYTE)
• For SQL Server: VARCHAR(200 BYTE)

I can connect to the Oracle DB via Oracle SQL Developer without any issues. Also, I have the SSIS package setting Run64BitRuntime = False.

Solution 1

The Oracle data type VARCHAR2 appears to be equivalent to NVARCHAR in SQL Server, or DT_WSTR in SSIS. Reference

You will have to convert using the Data Conversion Transformation, or CAST or CONVERT functions in SQL Server.

Solution 2

If the package works in one machine and doesn't in other; Try setting the NLS_LANG to right language, territory and character set and test the package.

[Command Prompt]> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Solution 3

The easiest way around this to to open the SSIS package in notepad (the dtsx file) and do a global find and replace of all instances of validateExternalMetadata="True" with validateExternalMetadata="False".

note: we encountered this issue when connecting to an Oracle 11g database on Linux through SSIS.

Solution 4

You can just double-click on the "Data Conversion" block in the Data Flow and for every item change it to: "Unicode String [DT_WSTR]"

Works

Solution 5

on oledb source ->advanced editor options->input/output columns->output columns->select RESOURCE_NAME column and change Data type as DT_WSTR and length also u can change as required

View more solutions

Comments

[Smandoli]

I believe .dtsx is correct. Make a back-up of the file before you touch it.

[Earth Engine]

This saved me! Thanks

[Patrick Schomburg]

Where can I find the advanced editor for an SSIS package?

[shockwave]

@PatrickSchomburg - In the Data Flow task, drag the ODBC source from Other Sources and right click the source. You'll find Advanced Editor.