Search code examples
ssishebrewcodepagesnon-unicodeoracle-rdb

SSIS Export to RDB doesn't work with NonUnicode Page File (8859-9)

I'm trying to export data from a table in MS SQL Server 2008 R2 to a RDB Database. But I'm having problems to export Hebrew strings to RDB because my SQL Server is Unicode and my RDB is Non-unicode.

Here are the details:

  1. I'm using Oracle RDB Data Provider for .NET RDB Connection

  2. I have a Non-Unicode Text field in the target table, and even if I convert the data to 28598 string, the data still get in Unicode format in RDB (and it becomes unreadable).

SSIS Data Conversion

Here are the results: RDB Results

  1. I've added a Grid Data Viewer to check if the data is going in the right format and it looks like it is. SSIS Grid Viewer

  2. I went even further and configured manually the ADO.NET External Columns property for this specific Column to match the data type (automatically he recognizes it as Unicode, which is not). ADO.NET Advanced Edit

For this I had check FALSE the External Data Validation. Otherwise SSIS would start to run. ADO.NET Advanced Edit part 2

If anyone have experience writing Non-Unicode data in RDB from SSIS please advise me. It can be Hebrew or any other language that uses Non-Unicode characters.

Solution

  • I found a solution to the problem. I replaced the connection provider from .Net Providers\ Oracle RDB Data Provider to ODBC.

    For this I had to download the ODCB driver for RDB Databases from Oracle web site (32 bit version) and configure a System DSN in Windows ODBC Data Source Administrator.

    SSIS Connection Manager & ODBC Data Source Administrator

    In my opinion there's a bug in the Oracle RDB Data Provider for .NET that only allows Unicode data.

    If Oracle updates this driver, I wish I could use the .NET provider instead of ODBC.