June 16, 2009 at 3:26 am
Hello ,
I am facing a strange problem when loading data from oracle to sql server 2005.
Oracle Db; Source
Table Src
Col1 varchar2(50)
Col2 varchar2(50)
SQL Server DB;Target
Table Tgt
Col1 varchar(50)
Col2 varchar(50)
When I trying to load data from Source to Target, it is throughing Unicode Error. "Cannot convert between Unicode and Non-Unicode String data types". I used SSIS for this.
Now I tried using Import Task from SSMS, to see when loading in target SQL SERVER automatically creates a table to load. and to my shock the table is created in SQL SERVER with the following structure for the same souorce given above
SQL Server DB;Target
Table Tgt
Col1 nvarchar(50)
Col2 nvarchar(50)
Kindly let me know the reason and how to fix it.
Thanks
Chandu
June 16, 2009 at 4:19 pm
Any time SSIS throws the Error: "Cannot convert between Unicode and Non-Unicode String data types", it means that the source and target datatype values are not the same. SSIS does a great deal of metadata validations within the data flow pipeline. You'll want to add in a data conversion step after your source data step to convert the unicode characters to the same data types as your destination metadata definitions.
June 19, 2009 at 7:42 am
Hi John,
The Really problem here is with the source adapter provider.
Oracle table datatype is varchar2(50) and when reading for OLE Provider for Oracle it is taking the column as nvarchar(50) and when reading from Microsoft Provider for oracle it is taking the column as varchar(50) which is correct. using these providers if we load the data into a flat file then the column datatype in the flat file shows DT_WSTR (OLE Provider for Oracle) and DT_STR(Microsoft provider for Oracle).
Kindly let me know the cause of this variation.
Thanks
Chandu
June 19, 2009 at 10:09 am
In your first explanation, you referenced moving data from an Oracle source to a SQL Server destination. Now you are talking about flat files? I'm not sure I follow you. Can you provide a detailed expanation of what you are trying to do, the steps you've designed to do it, and the problems you are having?
June 20, 2009 at 4:08 am
Hello Rowan,
Sorry for confusing you. My goal points are:
Load plan is
oracle --> flat file
flat file --> sql server
so if we take my first posting, then i think you can understand it better. let me know if you are still not clear.. I will provide you the complete story in one post
thanks
chandu
June 22, 2009 at 10:48 am
It's just a difference in how the data providers read the data. If you're reading it from flat file, it does not know what the original format was from the Oracle system. It can only tell what the data types are based on the flat file reader. As I said in my first post, you need to add a data conversion task to your SSIS data flow to cast the input columns from the flat file to the appropriate SQL Server data types that match your destination. That is how you fix it.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply