Search code examples
ssisssis-2012ssis-2008

Oracle 12c Number field mapped to DT_WSTR in SSIS

I am trying to extract the data from Oracle 12c database table to flat file using Microsoft SSIS 2008.

Table in Oracle Database

Create Table Test
(
  Col1 Varchar2(10),
  Col2 NUMBER default 0
)

There is no precision / scale is specified for the Col2 Number field. So it is storing the values as given.

Sample values

enter image description here

SSIS Package:

In SSIS package in OLEDB source component, we mapped the table and Col2 is mapped with DT_WSTR. And if i am changing to DT_Numeric, i am not sure on what to specify in the precision and scale. Since there is no precision / scale mentioned in Oracle table.

EDIT :

I am getting the error in OLE DB Source itself in the "Execute Phase" itself, [OLE DB Source 1] Error: There was an error with output column "COL2" (20) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

enter image description here

If i modify the data type in Output Columns of OLE DB Source like below, it is working, I have not modified in External Columns and OLE DB Error Output columns. In both the places it is DT_WSTR. Is this is ok?

And also i have not touched anything in Flat File Destination since i am getting exception in OLE DB Source.

enter image description here

Solution

  • In Oracle, when precision/scale is not explicitly included, the type NUMBER defaults to float:

    https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313

    so changing the DT_WSTR to DT_R8 (double-precision float) should be the safest assumption, you can do this in advanced editor:

    enter image description here