Home / Computer Science / Miscellenious / Step-by-Step Guide: Connect Oracle Database in SSIS Package (With Data Flow Task) – Boost Your Data Integration [2023]

Step-by-Step Guide: Connect Oracle Database in SSIS Package (With Data Flow Task) – Boost Your Data Integration [2023]

Connect Oracle Database in SSIS Package

Creating an Oracle “ora” file is a useful approach to manage database connection details for Oracle databases. This file simplifies the connection process by allowing users to reference a user-friendly alias, known as the Net Service Name, instead of directly specifying the connection details each time. In this guide, we will explain how to create an “ora” file step-by-step, providing a detailed overview of the necessary configuration.

Connect Oracle Database in SSIS Package

Creating TNSNAMES.ORA file

  1. Begin by opening a text editor, such as Notepad or any other preferred text editing software.
  2. Create a new, blank file to start the “ora” file.
  3. The first step is to specify the Net Service Name, which acts as an alias for the database connection. It enables users to refer to the database using a more recognizable name instead of the actual connection details. The format for specifying the Net Service Name is as follows:makefileCopy codeMYDB_CONNECTION = Replace “MYDB_CONNECTION” with the desired name for your database connection.
  4. Following the Net Service Name, you need to provide the connection details for the Oracle database. These details consist of several parameters that allow the application to connect to the database. The primary parameters to include are:
    • HOST: Specify the hostname or IP address of the Oracle database server.PORT: Specify the port number where the Oracle database is listening.SERVICE_NAME: Specify the service name or SID (System Identifier) of the Oracle database.USERID: Specify the username and password separated by a forward slash (/).
    Here is an example of including these parameters in the “ora” MYDB_CONNECTION = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydatabase) ) ) In this example, replace "myhost" with the actual hostname or IP address of the Oracle database server, "1521" with the appropriate port number, and “mydatabase” with the correct service name or SID.
  5. Save the file with a “.ora” extension. For instance, you can name it “mydb_connection.ora”.
  6. Optionally, you can include additional configuration parameters in the “ora” file to customize the connection behavior according to your requirements. These parameters might include connection timeout settings, encryption preferences, or other connection-specific configurations.
  7. Once the “ora” file is saved, you can utilize it to establish a connection to the Oracle database. Simply reference the Net Service Name specified in the file. This connection can be made using various tools and programming languages that support Oracle database connections.

By creating an “ora” file, you simplify the management of connection details for Oracle databases. With the Net Service Name acting as an alias, you can easily refer to the database without the need to repeatedly specify the connection parameters. This approach enhances efficiency and flexibility when integrating Oracle databases into your workflows, making the connection process smoother and more manageable.

In Windows, the “tnsnames.ora” file is typically saved in the “NETWORK\ADMIN” directory within the Oracle client installation folder. The exact location may vary depending on the Oracle client version and configuration. By default, the path to the “tnsnames.ora” file is as follows:

C:\oracle\client{client_version}\NETWORK\ADMIN\tnsnames.ora

Replace “{client_version}” with the actual version of the Oracle client installed on your system. If you installed the Oracle client in a different directory or customized the installation, the path may differ accordingly.

Example for TNSNAMES.ORA file is here

tnsnames.ora

ORCLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

SALESDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = saleshost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = salesdb)
)
)

Replace connection string with your database and port in the above file.

Previous article on oracle connectivity in ssis.

Official documentation on TNSNAMES.ORA file creation from Oracle corporation.

About Santosh Kumar Gadagamma

I'm Santosh Gadagamma, an Experienced Software Engineer passionate about sharing knowledge in technologies like Java, C/C++, DBMS/RDBMS, Bootstrap, Big Data, Javascript, Android, Spring, Hibernate, Struts, and all levels of software design, development, deployment, and maintenance. I believe computers are essential for the world's functioning, and I'm committed to helping others learn the skills they need to succeed in tech. My website is a valuable learning tool to help you reach greater heights in your education and career, and I believe that education has no end points.

Check Also

Pricing and Licensing of Microsoft Dataverse

Explore the various Microsoft subscription plans that include Microsoft Dataverse, such as Dynamics 365, Power Apps, and Power Automate. Understand how Dataverse offers flexible licensing options and seamless integration with existing Microsoft services, providing organizations with a cost-effective solution for consolidating data management and application development efforts.