Migration from Windows Oracle 12c to Linux Oracle 18c

0
votes

enter image description hereWe are migrating Oracle DB from 12c Windows Oracle to 18c Linux Oracle installation. Some of the columns (derived columns) in views has become unicode and not usable by downstream SSIS. Any reason why these views are giving error in SSIS? SSIS is complaining that in 18c columns have become unicode

12c View definition is enter image description here

18 c View defintion

enter image description here

1
oracleoracle18c
what's the actual SSIS 'complaint'? I'm assuming that's an error message? Also, it's safe to say your new 18c db has a new characterset compared to your old one, but please confirm – thatjeffsmith
Yes. Thats why we are struggling with multiple issues. Plus Linux added additional compatibility problems, 12 C -NLS_NCHAR_CHARACTERSET UTF8 --18 C NLS_NCHAR_CHARACTERSET AL16UTF16. SSIS 12C had data type of DT_STR and 18c its showing DT_WSTR. – SK AVVO
the NCHARSET won't matter if you're not dealing with NCLOB or NVARCHAR2 columns. So, are you not going to share with us the actual error you're getting in SSIS? – thatjeffsmith
attaching the error message in original message. It says Unicode to non unicode conversion is error – SK AVVO

1 Answers

0
votes

I think your problem matches this :

Oraoledb: Cannot Convert Between Unicode And Non-Unicode String Data Types (Doc ID 960508.1)

This is due to a difference in metadata reported by the OleDb provider depending upon whether NLS_LANG environment variable is set. Typically this behaviour would be observed when the SSIS package is developed in an environment that has NLS_LANG set, and then deployed to an environment that does not have it set. The difference in metadata results in the error, and is being investigated via Oracle bug number bug 7836009.

To resolve this issue, set NLS_LANG to the same setting as the box the package was developed on, which result in the same metadata being reported in both cases.The NLS_LANG environment variable should be set in the registry under

  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<homename>\NLS_LANG

Export the NLS_LANG variable in Linux with the same value as the original database

The behavior can also be worked around by refreshing the SSIS package after deployment, which will refresh the metadata.

Hope it helps. Regards.