SSIS Error: Can not convert between unicode and non-unicode string data types (Oracle to MS SQL 2008R2)
Friday, September 14, 2012 8:27 AM
By using SSIS (VS 2008), the target is to migrate data from a table in Oracle to a table in MS SQL 2008 R2.
In the Data flow Task, OLE DB Source gets the field in "string[DT_STR]" and the OLE DB Source filed in "Unicode string [DT_WSTR]". The process gets an error, which is: "Column XXX can not convert between unicode and non-unicode string data types".
[Source filed data type: Varchar2(30) and Target filed data type: NVarchar(30)]
Used a "data conversion" between source and target for the field XXX but it did not help.
Changing the data type of the field XXX in the target table to varchar can solve the problem. However, I want to keep on using nvarchar as the target field data type. Any ideas?
Friday, September 14, 2012 8:41 AMWhen you say Used a "data conversion" between source and target for the field XXX but it did not help. What error did you get when you used a data conversion trasnformation?
Friday, September 14, 2012 9:18 AM
I did not get any error in data conversion.
I get "Can not convert between unicode and non-unicode string data types"-error on the ole db target although I use a data conversion transformation.
When I execute the data flow task, i get a package validation error.
Friday, September 14, 2012 9:56 AM
Within the Data conversion transformation you have the Output Alias property. This is the name of the new column which will have your converted data. In you Ole DB target you have to map this new column to your destination to get rid of the problem. So if my data conversion looks like below
Then in the Ole Db destination i will map CNV_Column1 to the destination column instead of Column1.
Friday, September 14, 2012 10:03 AM
In Data Conversion task, which datatype you have selected for the respective column. It should be "Unicode string [DT_WSTR]".
Also, you need to map this new column [created from Data Conversion task] in destination task Mappings tab instead of actual source column.
Friday, September 14, 2012 10:15 AM
I do exactly the same thing: "Unicode string [DT_WSTR]" data type is chosen i data conversion transformation. This new "transformed" field is mapped to the respective field in the OLE Db target. (dtsx-file is saved. Restarted VS 2008. Then the dataflow task is executed. )
I keep getting the overmentioned errors.
Friday, September 14, 2012 10:21 AMWhen you mapped the new field to the destination column, did the ole db destination have the error bubble or was it gone. Hope you have matched the data length alongwith the data type. Can you post an image of your data conv, mapping and the error (using some editing in paint)
Friday, September 14, 2012 5:22 PMModeratorAnd do keep in mind that the Data Conversion transform doesn't change the data type "in-place" - it creates a new column. You need to map that new, differently named column to your destination.
Talk to me now on
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, September 20, 2012 2:43 PM