Solved

cannot convert between unicode and non-unicode data types error using SSIS to move data from SQL Server to oracle

Posted on 2008-10-27
5
3,972 Views
Last Modified: 2013-12-18
hi,
i'm trying to move data from SQL server 2005 to oracle 10g, i keep recieving the error message
cannot convert between unicode and non-unicode data types ,
the data type of the columns in error is nvarchar in SQL Server DB(Source DB), and its varchar2 in the oracle DB(Destination DB),i created a data conversion task between the source and destination and tried choosing DT_STR and DT_WSTR but that didnt help! still the same error message, should i choose a different data type, is there something else i need to do?
appreciate any suggestions :)
0
Comment
Question by:farahwj
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22813048
Hi,
This is a very common error in SSIS. nvarchar is Unicode and varchar2 in Oracle is non-unicode. In your data conversion task, modify the source column from DT-WSTR to DT_STR. See picture below. You could also use a CAST statement in your select to change the source column. This is my preference.
select CAST(unicode_col AS varchar(50)) non_unicode_col_now
If you choose this approach, delete the Data Flow Task and start over. This will clear any column definitions that might cause issues.
Good Luck, HZ
 

unicode-non-unicode-conversion.bmp
0
 

Author Comment

by:farahwj
ID: 22819497
thanks alot HZ, it works!
but can i ask another question related to this subject, the oracle DB and the SQL Server DB are different in structure, some columns in the Oracle DB dont have a match in the SQL Server DB and they are mandatory columns so i don't know how to deal with them!! i was wondering if there's a work around in SSIS for such cases!!
if you cant answer to this please inform me so that i can open a separate question for it, and answering it has nothing to do with awarding the points, i'll award them anyway!
 
thanx :)
 
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22820440
If I understand your second question, you need to put something into these "mandatory" columns in Oracle? You could add some constants to your select statement from SQL Server. For instance, if you need a column for SHOE_COLOR in Oracle but one does not exist in SQL Server, you could do this:
SELECT 'BLUE' as SHOE_COLOR, .... FROM ....
If the value in Oracle needs to be calculated (or derived, looked-up) from the values in your source, you could use a Lookup Transformation.
Does that help? Good luck!
0
 

Author Comment

by:farahwj
ID: 22820818
you understood my question right and it helps :) , but i'm not sure from where i can write a select statement using SSIS. First time using this tool, and i'm learning as i go!!
 
0
 

Author Comment

by:farahwj
ID: 22821828
i found from where thank you :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now