ODBC means Open Database Connectivity and it is a middle-ware or an interface that connects to various databases. In Informatica, ODBC connection is used to import source and target database table from database and add these source definitions to repository.

ODBC is an API that you can call from a variety of Programming languages on a variety of Operating systems. Learn how to create and add ODBC step by step

Creating ODBC Connection Informatica

ODBC connection for Source and Target can be made from System DSN tab, navigate to Start | Settings | Control panel | Administrative tools | Data sources (ODBC) | System DSN | Click on add for new connection.

Creating ODBC Connection Informatica

A pop up window will be opened, add new connection by adding new user DSN and select the desired database from the list and click on Finish button.

Creating ODBC Connection Informatica
  • Enter Data Source Name.
  • Enter Description.
  • TNS service name (XE default for Oracle 10g Database).
  • Enter User ID.
Creating ODBC Connection Informatica

Test connection and enter username and password of the Oracle database as shown below. Finally click on Ok button.

Creating ODBC Connection Informatica

Informatica always recommends to use Datadirect drivers for importing the source and targets. To add ODBC connection using Datadirect drivers follow the steps given below.

Creating source ODBC connection.

  • To create new ODBC source connection, click on Add button.
Source ODBC connection.

Select DataDirect 7.1 Oracle Wire protocol driver for which you want to set up a data source.

Source ODBC connection.
  • Click on Finish button.

Enter the following details to create new ODBC connection.

  • Data Source Name : SCOTT_ODBC_Oracle.
  • Host : localhost.
  • Port Number : 1521.
  • SID : ORCL.
creating new odbc connection
  • Click on Test connect, now a new window will be opened where user must enter username and password.
    • Enter username as SCOTT.
    • Password : Tiger.
Source ODBC connection.
  • Now click on OK button to test connection and message will be displayed as shown below.

We have created ODBC connection for source successfully, now create ODBC connection for Target as shown below.

Creating Target ODBC connection

  • Enter Data Source name as Batch10am_ODBC_Oracle.
  • Host : localhost.
  • Port : 1521.
  • SID : tutorialkart.
Creating Target ODBC connection

Click on Test connect button and enter user name and password and finally click on Ok button.