Oracle Destination

Overview

Oracle Destination Component is SSIS Data Flow Component, which connects to Oracle database and bulk loads data into Oracle tables. It uses a direct path load approach. Using direct path load is faster than the standard loading (using the OLE DB Destination) as it formats Oracle data blocks and writes the data blocks directly to the data files. This eliminates considerable processing overhead. The configuration options are provided in the standard data flow destination component dialog. The component requires installation of Oracle 11g ODAC 11.1.0.6.20.

Demonstration

Setup

Use the options below to setup the component.

Options

IDbConnection

Select an existing ADO.NET Oracle Data Provider for .NET connection manager, or click <New connection...> to create a connection manager.

Related Topics: ADO.NET Connection Manager

DestinationTable

Specify the destination database table where the data is loaded.

DestinationPartition

Specify the destination table partition where the data is loaded. This property is not required if destination is single partition table.

BatchSize

Specify the number of rows to be sent as a batch.

Timeout

Specify the number of seconds allowed for the bulk copy operation to complete before it is aborted.

How to modify destination table dynamically at runtime?

The destination table is available in the data flow properties list. Follow these steps to setup expression:

  • Right-click on data flow canvas and select Properties menu.
  • Scroll-down and find property named like [Oracle Destination].[DestinationTable]. This is the property containing the destination table.
  • Scroll-down and find Expressions property. Setup expression to modify the statement dynamically.

Samples

  • To test the performance of Oracle Destination against the standard OLEDB Destination, check this package. You have to create TEST_TABLE table with following columns:
    • COL1 NUMBER
    • COL2 TIMESTAMP
    • COL3 FLOAT
    • COL4 NVARCHAR2 (250)
    • COL5 NVARCHAR2 (250)

    NumberOfRows variable controls the amount of inserted rows.