Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical 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.
An Oracle Connection Manager is used to enable a package to extract data from Oracle Databases and load data into Oracle Databases.
The ConnectionManagerType property for the Oracle Connection Manager is set to ORACLE.
In SSIS execution logs, this connector is referred to as "Oracle Connection Manager."
Oracle Connection Manager configuration changes will be resolved by Integration Services at runtime. Use the Oracle Connection Manager Editor dialog box to add a connection to an Oracle data source.
Enter information about the Oracle connection.
Name
Input a name for the Oracle connection. The default name is Oracle Connection Manager.
Description
Input a description of the connection. This input is optional.
TNS service name
Input the name of the Oracle database you work with. The TNS service name could be:
The connection name defined in the tnsnames.ora file
EzConnect format: [//]host[:port][/service_name]
To use a tnsnames.ora file, you may need to add a system environment variable to the machine running the SSIS package. The TNS_Admin environment variable specifies the location of the folder that contains the tnsnames.ora file. This is required if you haven't installed an Oracle client. To add the environment variable in Windows 10, Windows 11 or Windows Server 2022:
For more information, see the Oracle documentation.
Select one of the below options:
Use Windows Authentication: Select this to use Windows authentication.
Use Oracle Authentication: Select this to use Oracle database authentication. If you use this authentication, enter your Oracle credentials as follows:
User name: Type the user name used to connect to the Oracle database.
Password: Type the Oracle database password for the user entered in the user name field.
Note
Windows Authentication isn't supported for Oracle Server 18c.
Test Connection
Click Test Connection to verify if the information provided is correct. You'll receive the message Test connection succeeded, if the information entered is able to connect to the Oracle database.
Note
To specify ConnectionString directly, here's a sample with Oracle Authentication:
SERVER=\<YourOracleServerName or EzConnect format>;USERNAME=\<YourUserName>;PWD=\<YourPassword>;WINAUTH=0
There are following custom connection manager properties in the Oracle connection manager:
EnableDetailedTracing: Not Used.
OracleHome: Specify 32-bit Oracle Home name or folder to be used by the connector. (Optional)
OracleHome64: Specify 64-bit Oracle Home name or folder to be used by the connector when running in 64-bit mode. (Optional)
Custom properties aren't listed in Oracle Connection Manager Editor. To set the OracleHome and OracleHome64 properties:
From the Connection Manager area, right-click the Oracle connection manager you're working with and select Properties.
In the Properties pane, set the OracleHome or OracleHome64 property with the full path to the Oracle home directory.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Migrate Oracle workloads to Oracle Database@Azure - Training
Learn how to migrate your Oracle workloads to Oracle Database@Azure.
Documentation
Microsoft Connector for Oracle - SQL Server Integration Services (SSIS)
Microsoft Connector for Oracle
Oracle Destination - SQL Server Integration Services (SSIS)
Oracle Destination
Oracle source - SQL Server Integration Services (SSIS)
Oracle source