2

We have an SSIS package on our Virtual Machine(assume this as VM1) where we are pulling data from Oracle source. The data type in Oracle for the column is Varchar2 and here in SSIS it's pulling as DT_WSTR data type and storing the data as NVarchar column. When I open the same package from different Virtual Machine(assume this as VM2), the SSIS package is pulling as DT_STR data type and the package is failing due to conversion error in the validation phase of SSIS package. I'm also getting a warning which is pasted below when I click on columns in Data Flow Task of Oracle source SSIS package.

Warning - Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

We have Oracle Java(JDK) and Oracle client installed on both VM1 and VM2. The OS on our VMs is Windows 7 and SSIS packages are of Visual Studio 2013 on both VMs.

Jacob H
  • 2,455
  • 1
  • 12
  • 29
kartheek baddula
  • 23
  • 1
  • 4
  • 1
    I have tried installing ''ODAC 12c Release 2 and Oracle Developer Tools for Visual Studio (12.1.0.1.2)'' and that fixed the conversion issue. Now, Varchar2 is read as DT_WST and the Warning message of Default Code Page is also fixed. This page helped me https://www.inogic.com/blog/2016/12/steps-to-connect-ssis-to-oracle/ – kartheek baddula Jan 25 '20 at 18:00

1 Answers1

0

I have had to deal with similar datatype issues between Oracle and SSIS. And with SSIS being so finicky about datatypes, I had to find a solution to implement on the Oracle side.

Before I explain my answer, I should mention that I use the Attunity Connectors for Oracle from Microsoft. I highly recommend using these connectors over the default connects Microsoft and Oracle provide.

So, with that said, I have found two techniques that seem to work to pull data over in the correct encoding. SSIS is really bad at reading and translating metadata from the Oracle system, but explicitly CASTing to a VARCHAR2, even if the column is already a VARCHAR2, seems to be enough of a hint that SSIS knows that column will be a DT_STR type. In all of my Oracle Source tasks, I use a SQL Command rather than just choosing the table (it's a best practice), and that allows me to add in the CAST to the query. For a VARCHAR2 column, I'd do something like this:

SELECT CAST("PO Number" AS VARCHAR2(30)) AS "PONumber" FROM TABLE1

This will usually be enough. But sometimes it won't be, because Oracle allows for some weird characters in a VARCHAR2 column. If you see the error [Oracle Source [2345]] Error: OCI error encountered. ORA-29275: partial multibyte character even after explicity CASTing your column to VARCHAR2, this is due to a code page mismatch. To correct it you can CONVERT the character encoding of the string like this:

SELECT CONVERT("PO Number",'AL32UTF8','WE8MSWIN1252') AS "PONumber" FROM TABLE1

AL32UTF8 is the default (Unicode) encoding that Oracle uses, and WE8MSWIN1252 is the default (ASCII 1252) encoding used by Windows systems.

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • 1
    Thank you @digital.aaron for your inputs. I have tried installing ''ODAC 12c Release 2 and Oracle Developer Tools for Visual Studio (12.1.0.1.2)'' and that fixed the conversion issue. Now, Varchar2 is read as DT_WST and the Warning message of Default Code Page is also fixed. This page helped me https://www.inogic.com/blog/2016/12/steps-to-connect-ssis-to-oracle/ – kartheek baddula Jan 25 '20 at 18:03