Load data from or into Oracle database

This document focuses on how to export data from and load data into Oracle data database using the SSIS ADO.NET source and ADO.NET Destination components. An ADO.NET connection manager allows a package to access data sources with a .NET provider, specifically the OracleClient Data Provider.

Here are the main steps.

Install Oracle Client

First, download the latest Oracle Client for Microsoft Tools and install Oracle client.

Note

Installing both 32-bit and 64-bit versions together may cause compatibility issues. Install only one version.

Configure ADO.NET connection manager

  1. Create ADO.NET connection for Oracle connection
  2. Choose .Net Providers\OracleClient Data Provider, then enter server name, username, and password.

Screenshot of ado.net connection manager.

For details, see ADO.NET connection manager.

Configure ADO.NET Source

Export data using the ADO NET source. For details, see ADO NET Source.

Screenshot of ado.net source.

Configure ADO.NET Destination

Use ADO NET destination loads data into ADO.NET-compliant databases. For details, see ADO NET Destination.

Screenshot of ado.net destination.

Note

ADO.NET allows packages to access data sources with the .NET OracleClient Data Provider. When migrating from Microsoft's connector for Oracle, custom properties in Oracle source and Oracle destination components set by Advanced Editor only aren't available in ADO.NET source and destination.