Steps To Connect SSIS to Oracle

By | December 26, 2016

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.

13) 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”.

2

  • Go to Advance tab and click on Environment Variables.

3

  • 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.

4After 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.

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

6You 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.

7Then 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.

Need help with migration from old Dynamics CRM version or On-Premises to Dynamics 365/CRM Online, we can assist!

We would like to take a moment to introduce our new product Alerts4Dynamics to you. Alerts4Dynamics lets you schedule and manage alerts in Dynamics 365 CRM to notify users about the updates in CRM, due invoices, reminder to send quotes, etc. You can define target audience and send them priority based alerts via pop-ups, form notifications and emails. You can also view the log of read/dismissed alerts by users and also create alerts for multiple records by defining rules.

4 thoughts on “Steps To Connect SSIS to Oracle

    1. inogic Post author

      Hi,

      It seems that you want to set oracle connection dynamically from a particular file location. This means SSIS package will point to the file in which various connection details are stored.

      So in future, you can just edit /update the file without making any changes in SSIS.

      In order to achieve this you have to create dynamic oracle connection. For this you can use the Project.params of SSIS Package.

      For ref please check the below link:

      https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-package-and-project-parameters?view=sql-server-2017

      Hope this helps.

      Thanks!

      Reply
  1. edmond

    Hi, I have questions on server name and Service_name below:
    Can you explain which server name/ip I need to put in?
    What is service name?

    CONNECT_DATA = (SERVER = your server address (e.g. 157.20.4.148))
    (SERVICE_NAME = your service name)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *