0

I have SQL Server 2008 R2 and I am using SSIS to import date from another server (SQL 2005) to my production server. When I import Arabic characters, it is appearing as question marks while it is working on test environment.

I checked all the settings and collations for the server, DB, tables and columns. everything is identical. I believe that it is not a matter of coding since it is working on test and I copied the same SSIS package to be used on production.

The source columns have NVARCHAR as datatype while the destination have VARCHAR. I created a simple query to select the data from the source and insert it to destination and it is working without any problem but when I am using SSIS to import the date, it is not working.

Any idea?

user2369934
  • 1
  • 1

1 Answers1

0

I've been facing the same issue and breaking my head to find a workaround. After several trial and error methods, the below step worked for me:

Issue

The driver interprets the data from Oracle DB as DT_STR type and does not support Arabic characters. To solve this we need the driver to read the data as DT_WSTR type.

Solution

So the solution is we need to make SSIS engine to read the data as DT_WSTR and not DT_STR.

VS Solution

I was using Microsoft OLE DB provider for Oracle as a provider in connection manager and was facing this error.

VS provides another driver to connect to Oracle DB, Oracle Provider for OLE DB. This driver reads the data in DT_WSTR format.

When I created the data source using the Oracle Provider for OLE DB the data is being read in DT_WSTR format and successfully pushed to the destination in the same character as in the source.

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Moulitharan M
  • 729
  • 4
  • 15