Moving data from Oracle to SQL Server

Question

Post reply

Moving data from Oracle to SQL Server

sqldba_VA

SSCommitted

Points: 1937
More actions
September 23, 2010 at 9:12 am

#143471

Hi all,
I had tried DTS, SSIS and direct query to import data from Oracle server to SQL Server. 70 % data comes clean as expected but 30 % data had issue. This 30 % data (in two columns) had chinese / Korean charecters in Oracle. When data moved to SQL Server, I see the data in two columns as junk characters such as '¿¿¿¿¿¿¿¿¿¿¿'.
In oracle datatype is Varchar2(100) and SQL Server data type is NVarchar(255). The query I am using to move data looks like this.
Insert SQLSERVER_TABLE
SELECT *
FROM OPENQUERY(MSODRA, 'SELECT * FROM ORASCHEMA.ORATABLE')
GO
I tried to test unicode data in sql server and got results as expected. If I use N'unicode data' while inserting into sql server I get it right. But, don't know how to use N'Unicode data', while extracting and direct insert into sql server using above query.
Can some one please help.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sqldba_VA SSCommitted Points: 1937 More actions · Answer 1

The code I tested for unicode insert in SQL Server is

create table yourtable (yourid int,yourvalue nvarchar(200))

go

insert into yourtable (yourid, yourvalue) values (1,'user1')

insert into yourtable (yourid,yourvalue) values (2,'user2')

insert into yourtable (yourid,yourvalue) values (3,'??')

insert into yourtable (yourid,yourvalue) values (4, N'??')

select * from yourtable

go

drop table yourtable

go

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

For converting into the unicode, you can use a data conversion transformation between the OLEDB Source and OLEDB destination and the in that data conversion transformation change the destination type to unicode.

Thank You,

Best Regards,

SQLBuddy

sqldba_VA SSCommitted Points: 1937 More actions · Answer 3

sqlbuddy123 (9/23/2010)
For converting into the unicode, you can use a data conversion transformation between the OLEDB Source and OLEDB destination and the in that data conversion transformation change the destination type to unicode.
Thank You,
Best Regards,
SQLBuddy

Destination is already set to Unicode ie, nVarchar(255). Tried it to convert by data conversion transformation but could not.

Can you please explain or give more details about how to do that.

Thanks for response.

sqldba_VA SSCommitted Points: 1937 More actions · Answer 4

Any one here ?

I will rephrase my question again.

I want to move data from Oracle using DTS package to SQL Server.

Two column has chinese / korean data and after data moved to SQL Server I see chinese / korean data as junk charecters.

My quesiton:

How can I see chinese / korean data in sql server?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

1. Select a Dataflow Task

2. Double Click on it and then add OLEDB Source and OLEDB destination.

3. Put a Data Conversion Task in between OLEDB Source and OLEDB destination.

4. Double Click on the Data Conversion Task and select the column that needs to be coverted.

5. If you want to rename the column then give an appropriate name to the Output Alias and change the datatype to Unicode string[DT_WTSR] and select an appropriate length for the output column.

6. Double click on the OLEDB Destination and go to Mappings. Select the modified colum in the data conversion task as the InputColumn from the drop down box and map it to the actual destination coulmn with Unicode datatype.

7. Run the Package

Thank You,

Best Regards,

SQLBuddy

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

I had the same issue some time back and had to spent more than three days to know the cause.

First thing first change the data type to Nvarchar in the destination column.

And the most important one is not to use Microsoft OLE DB provider for Oracle for connecting to Oracle database.

Create a system DSN and not user DSN in ODBC and use ODBC driver (should be something like Oracle in Home1). Provide all credentials to DNS and use it as source in SSIS or DTS.

execute it and it should work. Another thing, change the result font type to Courier or Ariel unicode and you would see result in Chinese or Korean data set.

SQL DBA.

sqldba_VA SSCommitted Points: 1937 More actions · Answer 7

Thanks for replies to SQLBuddy and Sanjay.

I followed steps by Sanjay and it worked perfectly as mentioned. I can now see chinese and Korean data in columns.

Thanks again.

SanjayAttray SSChampion Points: 13157 More actions · Answer 8

SanjayAttray

SSChampion

Points: 13157

September 28, 2010 at 11:29 am

#1228555

Glad it worked.

SQL DBA.