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,976 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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