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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

732 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