none
unicode to nonunicode conversion

    Question

  • hi ,

    im actually redesigning a package which was earlier using datareader,now im using OLEDB provider for Oracle.

    problem is i have more than 50 tables which are of predefined structure which was formed by datareader.

    where all the datatypes were varchar in target,

    now when im using OLEDB it is throwing exception to convert to Unicode string.

    i know that i have to use dataconversion transformation to do this,

    but for all 40 tables it is difficult and time consuming.

    so any other way to do it ?

    Wednesday, December 28, 2016 2:21 PM

All replies

  • Some Oracle drivers always return the data as Unicode.

    Please see "AlwaysUseDefaultCodePage" setting:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc1a61f2-1ab8-4ed3-b85c-db6481800b50/error-importing-data-from-oracle-database-to-an-sql-database?forum=sqlintegrationservices

    Wednesday, December 28, 2016 4:26 PM
  • this Property (AlwaysUseDefaultCodePage) already used for OLEDB Source transformation it resolves the warning,

    but actual problem is target tables i already have are of varchar data type.

    so it is showing exception for OLEDB Destination transformation.

    Thursday, December 29, 2016 8:58 AM
  • If you have Unicode data and try to put it into varchar, there will be data loss, unless you have made an investigation and certified that all characters are within the code page of the target collation. I guess you would not get these exceptions, if this was the case.

    Any particular reason you have varchar and not nvarchar?

    What is the target collation? What language(s) is the source data typically in?

    Thursday, December 29, 2016 10:04 AM
  • not sure how to check the collation of oracle source system,

    but here im attaching screen shot of NLS 

    Thursday, December 29, 2016 10:26 AM
  • im not Oracle Expert but To_Char conversion for field level working,

    but it is difficult to apply to more than 1000+ fields.

    Thursday, December 29, 2016 10:28 AM
  • im not Oracle Expert but To_Char conversion for field level working,

    Working in the sense that you get no errors. But is it working in the sense that there is no data loss?

    Thursday, December 29, 2016 11:37 AM
  • You are getting the error because your SOURCE is Unicode and you are trying to put it into non-Unicode fields.

    If you did not select AlwaysUseDefaultCodePage when you created the source, SSIS "helped" you by marking all the fields as Unicode. Setting the flag after does not reset all the fields. You will need to delete the source and recreate it.  It is way too difficult to fix all the problems caused by SSIS helping set the data types.

    Thursday, December 29, 2016 12:49 PM