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.
... or - ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.0).
Sample package
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.
Demonstration
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing ADO.NET Oracle Data Provider for .NET connection manager, or click <New connection...> to create a new connection manager. If you use ODAC 12.2c Release 1 you have to use ODP.Net, Unmanaged provider.
Related Topics: ADO.NET Connection Manager
Specify the destination database table where the data is to be loaded.
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.
Specify the number of rows to be sent as a batch.
Specify the number of seconds allowed for the bulk copy operation to complete before it is aborted.
Knowledge Base
- Where can I find the documentation for the Oracle Destination?
- Why do I get the following error during data flow execution?
- Why do I get the following error when executing a package under SQL Server Agent Job or DTEXEC?
- Why do I get the following error when executing a package under Business Intelligence Development Studio (BIDS)?
- Oracle Destination: How to modify the Oracle destination table dynamically at runtime
What's New
- New: Component is no longer dependent on specific version of Oracle Data Provider for .NET.
- Fixed: Support for writing BLOB columns.
- Fixed: Validation was incorrect.
- Fixed: Component failed with NullReferenceException error, when trying to bulk-insert columns with NULL values.
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.