Menu
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:
VARCHAR2(200 BYTE) 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.
255
The only difference between the Unicode and the non-Unicode versions is whether OAWCHAR or char data type is used for character data. The length arguments always indicate the number of characters, not the number of bytes.
Unicode Data Types. Data types nchar, nvarchar, and long nvarchar are used to store Unicode data. They behave similarly to char, varchar, and long varchar character types respectively, except that each character in a Unicode type typically uses 16 bits.
Implicit Conversion Implicit conversions are not visible to the user. Data types are automatically converted from one data type to another. For example, when a string is compared to an int, the string is implicitly converted to int before the comparison proceeds: 1.
By default Excel data is treated as Unicode and also by default when you create new tables SQL Server will make your character type columns Unicode as well (nchar, nvarchar,etc...)
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.
190
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
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.
35
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
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
