Oracle Destination

Overview

The Oracle Destination Component is an SSIS Data Flow Component, which is used to connect to an Oracle database and to perform bulk loads of data into Oracle tables. It uses a direct path load approach. Using a direct path load is faster than 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 parameters are provided in the standard data flow destination component dialog. This component requires installation of Oracle 11g ODAC 11.1.0.6.20.

Demonstration

Setup

Use the parameters below to setup the component.

Parameters

IDbConnection

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

Related Topics: ADO.NET Connection Manager

DestinationTable

Specify the destination database table where the data is to be loaded.

DestinationPartition

Specify the destination table partition where the data is to be loaded. This property is not required if the destination is a 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 the destination table dynamically at runtime

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

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

Samples

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

    The NumberOfRows variable controls the number of rows inserted.