Edit

Share via

Microsoft Connector for Oracle data type support

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

Important

Microsoft Connector for Oracle is deprecated now. Details refer to the announcement.

SSIS components for Oracle don't support all Oracle data types. Columns with unsupported data types have a warning when designing packages in SSDT and will be deleted from mapping columns. Data can't be loaded to a column with an unsupported data type.

Data type mapping

The following table shows the Oracle database data types and their default mapping to SSIS data types. It also shows the unsupported Oracle data types.

Oracle Database Data Type SSIS Data Type Comments
VARCHAR2 DT_STR
NVARCHAR2 DT_WSTR
CHAR DT_STR
NUMBER DT_R8 This can be changed to DT_NUMERIC with specific precision and scale. Precision and scale are defined by user per the need. The output is the column data as a number with fixed precision and scale.
NUMBER(P, S) When the scale is 0, according to the precision (P)
  • DT_I1
  • DT_I2
  • DT_I4
  • DT_NUMBERIC(P,0)
    		•
    DT_NUMERIC(P,S)
    DATE DT_DBTIMESTAMP
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • TIMESTAMP WITH LOCAL TIME ZONE
    		•  DT_STR
    RAW DT_BYTES
    CLOB DT_TEXT CLOB, NCLOB, and BLOB data types are supported only in array mode, not in Fast Load mode.
    NCLOB DT_NTEXT
    BLOB DT_IMAGE
    UROWID Not Supported
    REF Not Supported
    BFILE Not Supported
    LONG Not Supported
    LONG RAW Not Supported
    ROWID Not Supported
    User-defined type (object type, VARRAY, Nested Table) Not Supported

    Next steps