Steps To Connect SSIS to Oracle

By | December 26, 2016


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.


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.

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

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.


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.

4 thoughts on “Steps To Connect SSIS to Oracle

  1. Triveni

    How to provide oracle connection dynamically in ssis

    1. inogic


      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:

      Hope this helps.


  2. 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.
    (SERVICE_NAME = your service name)

Comments are closed.