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,978 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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