Steps To Connect SSIS to Oracle

Introduction

In SSIS, if you want to migrate the data from Oracle to Microsoft Dynamics CRM we need to connect to the OLA DB source to execute the Oracle queries. This blog will discuss how to connect to the Oracle server.

Prerequisite:

Install the software mentioned below on the client machine:

1) Oracle 11g Database express edition (OracleXE112_Win64) – We need this because when we install this software, it automatically installs the “Oracle Data Provider For .Net”. And this provider is needed for SSIS Tool.

2) ODAC 32 and 64 bit:

       ODAC 64 bit driver is installed using .exe file.

Install ODAC 32 bit driver using CMD as shown in the screenshot:

• Go to ODAC 32 bit folder where the install.bat file is located using CMD.
• Type “install.bat all c:/oracle” command and press Enter.
• Installation file will be located at “c:/oracle” folder.

3) Install Oracle 11g client 32 and 64 bit:

After installation of the client, you will find the “tnsnames.ora” file at “F:\app\product\11.2.0\client_2\Network\Admin\Sample” path

Connection Steps:

1. Edit the “tnsnames.ora” file.

             Local_name =

           (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP)(HOST = your host Address (e.g. 183.516.6.51))(PORT = 1521))

           (CONNECT_DATA =

            (SERVER = your server address (e.g. 157.20.4.148))

            (SERVICE_NAME = your service name)

           )

    )

2. After performing the above steps, you need to add the Environment variable on client machine. Please follow the below steps to add environment variable.

• Go to System Setting and click on “Advance system settings”.

• Go to Advance tab and click on Environment Variables.

• Add new system variable and give values as below:

Variable Name – TNS_ADMIN

Variable Value – “file path where we store the tnsnames.ora” for example We have installed the Oracle 11g client at: “F:\app\user\product\11.2.0\client_2\Network\Admin\Sample” path and “tnsnames.ora” file is located at this path.

After adding the Environment variable, restart the machine.

3. After restarting the machine, open the Visual Studio New SSIS project as shown in the below screenshot.

After creating project, drag the Data Flow Task Control. In the Data flow task control you need to add the “OLE DB Source” control. Please see the below screenshot.

Edit the OLE DB source. Click on New >> Click on New to configure the ODBC connection. Please see the below screenshot.

You will get the below screen. Select the “Native OLE DB\Microsoft OLE DB Provider for Oracle”. Enter the server name, User Name, and Password. Please see the below screenshot.

Then click on Test Connection. Now you will able to connect and access the Oracle server.

Conclusion

In this way, you can connect to Oracle server to migrate the data from Oracle to Microsoft Dynamics CRM.

One Pic = 1000 words! Analyze data 90% faster with visualization apps!

Get optimum visualization of Dynamics 365 CRM data with –
Kanban Board – Visualize Dynamics 365 CRM data in Kanban view by categorizing entity records in lanes and rows as per their status, priority, etc.
Map My Relationships – Map My Relationships – Visualize connections and relationships between Dynamics 365 CRM entities or related records in a Mind Map view.