4

We have an SSIS package downloading data from an Oracle database to an SQL Server datawarehouse. For this datawarehouse, several environments are set up; Development, Test and Production. Dev and test share a machine, Prod is stand-alone.

When the SSIS package is run on the PROD machine, it downloads the Varchar2 columns from our Oracle source database to MSSQL in DT_WSTR format and saves this to a NVarchar column. I.E. all steps involved support Unicode.

When this same package is run against the same source database on the DEV/Test box, it somehow sees the external columns as being Varchar, derives this to DT_STR in the data flow and refuses to store this in an NVarchar column.

All OS's are Win2K8r2, MSSQL 2008 64 bits. The package is run in 32bits mode, same behaviour is seen when run from BIDS or from SQL Agent.

Anyone care to guess why? I've already seen the suggestion to disable validating external metadata (https://stackoverflow.com/a/18383598/2903056), but that's not a practical suggestion for our situation.

Community
  • 1
  • 1
steenbergh
  • 1,642
  • 3
  • 22
  • 40
  • Try Text, instead of DT_STR – Zerotoinfinity Oct 21 '13 at 12:59
  • 1
    I'm sorry, but changing the contents of the package is not an option (at this time). I believe that if 1 box can do this correctly, the other can too. It's just that this problem acts up in a lot of packages... – steenbergh Oct 21 '13 at 13:09
  • Is the Oracle source the same for each environment? Compare your SQL Server locale / collation? – gvee Oct 21 '13 at 15:16
  • Yes, the Oracle source is the same database on the same server in both cases. The SQL Servers have the same collations and locale settings; the only exception is the Locaton setting on our PROD machine. This is set to USA, DEV is set to the Netherlands. I have manually edited the registry to set all NLS_LANGUAGE keys to AMERICAN. – steenbergh Oct 22 '13 at 07:46
  • When you look at the advanced properties of the Output columns of the Source transformation, what SSIS datatype is the varchar2 column designated to output as? Did you double check to be sure it's the same in both environments? – Tab Alleman May 12 '15 at 19:17

3 Answers3

2

An old question I know, but seems to still be relevant. And since I could not find a suitable answer in the last 3 months I have been searching, I figure now is as good a time as any to post my findings.

I have had the same curious behaviour and have finally been able to resolve it. My layout looked like this:

  • Oracle 10g R2 database on Windows 2003 Server (lets call it ORA)
  • Dev machine with Windows 8, Visual Studio 2012 + SSDT, Sql Express 2012, ODT 12.1.0.21 (lets call that DEV)
  • Sql Server 2012 on Windows 2012 Server, Oracle Client 11.2 (lets call that TEST)

Both DEV and TEST were connecting to ORA. DEV was reporting VARCHAR2 columns as DT_WSTR while TEST would insist that they are DT_STR.

I then installed ODT 12.1.0.21 on TEST and the problem was solved. Notably, I used the "machine wide" option during the install. I am not sure how much of an impact that had.

There seems to be a difference in the datatypes that are returned by the Oracle OleDb providers across the different versions of the client side components.

dayneo
  • 482
  • 5
  • 12
1

Check the value of the NLS_LANG in the registry.

reg query HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_<orahome> /f NLS_LANG

If it matches the server's character set, OraOLEDB will use regular (non-Unicode) datatype DBTYPE_STR, otherwise it uses Unicode-mode, datatype DBTYPE_WSTR.

If the NLS_LANG field is missing, it defaults to US7ASCII which almost certainly will not match your database and you will be using Unicode datatypes.

To get the server's characterset, do:

SELECT parameter, value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
Pang
  • 9,564
  • 146
  • 81
  • 122
M Landry
  • 11
  • 1
-2

Check Metadata validation property value if it true make it false

Kishor K
  • 1
  • 5