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.
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.
- Enter Data Source Name.
- Enter Description.
- TNS service name (XE default for Oracle 10g Database).
- Enter User ID.
Test connection and enter username and password of the Oracle database as shown below. Finally click on Ok button.
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.
Select DataDirect 7.1 Oracle Wire protocol driver for which you want to set up a data source.
- 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.
- Click on Test connect, now a new window will be opened where user must enter username and password.
- Enter username as SCOTT.
- Password : Tiger.
- 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.
Click on Test connect button and enter user name and password and finally click on Ok button.