DogBytes
asked on
Exporting Unicode string to Oracle database SSIS conversion error
Hi,
I am moving data from a SQL Server 2005 database to an Oracle 11g database using SSIS. I am having trouble sending unicode columns over.
I am trying to send an SQL Server NVARCHAR(50) to Oracle NVARCHAR2(50) as I'm presuming that is the data type I should be using in Oracle ?
But I get the "Cannot convert between Unicode and non-unicode string data types" SSIS error. I thought that it should recognise the Oracle NVARCHAR2 as unicode.
Any help much appreciated. Do I need to do a conversion before sending it to Oracle ?
I am moving data from a SQL Server 2005 database to an Oracle 11g database using SSIS. I am having trouble sending unicode columns over.
I am trying to send an SQL Server NVARCHAR(50) to Oracle NVARCHAR2(50) as I'm presuming that is the data type I should be using in Oracle ?
But I get the "Cannot convert between Unicode and non-unicode string data types" SSIS error. I thought that it should recognise the Oracle NVARCHAR2 as unicode.
Any help much appreciated. Do I need to do a conversion before sending it to Oracle ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
On my Oracle database, when I do :
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
I get :
AL32UTF8
And for
select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';
I get:
AL16UTF16
Here is what my destination Oracle table looks like:
PROGRAMME_KEY NOT NULL NUMBER
PROGRAMME_ID NOT NULL NUMBER
PROGRAMME_NAME NVARCHAR2(100)
PROGRAMME_NAME on my source SQL Server table is NVARCHAR(50)
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
I get :
AL32UTF8
And for
select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';
I get:
AL16UTF16
Here is what my destination Oracle table looks like:
PROGRAMME_KEY NOT NULL NUMBER
PROGRAMME_ID NOT NULL NUMBER
PROGRAMME_NAME NVARCHAR2(100)
PROGRAMME_NAME on my source SQL Server table is NVARCHAR(50)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you - that has worked...
Just one more thing that's bugging me on this though... Every time i open up my Oracle OLE DB destination object in SSIS I get a message about it not being able to retrieve the column code page info (message print attached).
Is this something I should be concerned about? Is there something I need to change?
If not, is there a way to stop this message popping up all the time ?
ssis-error.JPG
Just one more thing that's bugging me on this though... Every time i open up my Oracle OLE DB destination object in SSIS I get a message about it not being able to retrieve the column code page info (message print attached).
Is this something I should be concerned about? Is there something I need to change?
If not, is there a way to stop this message popping up all the time ?
ssis-error.JPG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great - thanks everyone for your help on this.
Run a DESCR on the table in oracle and post here.
Thanks,
HZ