Using Easysoft ODBC Drivers with Informatica PowerCenter

Informatica can use both native drivers and third party drivers to communicate with databases. Native drivers are provided by the database vendor, for example, Oracle's OCI libraries or Microsoft's SQL Server Native Client. Native drivers are Informatica's recommended data access method, when available for your Informatica platform.

The Informatica distribution includes third party drivers for a number of databases. These third party drivers use ODBC to communicate with databases. ODBC is a database-neutral interface that allows ODBC compliant applications such as Informatica to work with any database for which an ODBC driver is available. ODBC translates Informatica's data queries into something the target database understands.

ODBC has two components: the ODBC driver and the ODBC Driver Manager. The ODBC driver is database-specific, i.e. a Microsoft Access ODBC driver will only talk to a Microsoft Access database. The ODBC Driver Manager is the interface between Informatica and the ODBC driver. The Driver Manager is responsible for loading the ODBC driver, insulating Informatica from the component that interacts with the database. This architecture enables Informatica to connect to different databases without any changes being made to the application.

On Windows, Microsoft provide an ODBC Driver Manager with the operating system, and this is the one that Informatica uses on this platform.

On UNIX and Linux, the Informatica distribution includes an ODBC Driver Manager.

So we know that the ODBC architecture allows an application to support multiple database backends by plugging in different database-specific ODBC drivers. How does this work in practice with Informatica? What do you need to do to enable Informatica to use ODBC drivers that are not bundled with the application.

On Windows, this is straightforward. You install the driver under the Microsoft ODBC Driver Manager, and configure an ODBC data source in the ODBC Data Source Administrator applet, which is located in the Windows Control Panel. The ODBC data source is then available for use in Informatica; no further configuration is required. A common source of confusion on 64-bit Windows machines is the "Architecture Mismatch" error that you get if you attempt to use a 64-bit ODBC driver with a 32-bit application or vice versa. If your Informatica client application is 64-bit, you must use it with a 64-bit ODBC driver and configure a data source in the 64-bit version of ODBC Data Source Administrator. The 64-bit ODBC Data Source Administrator will either be the only ODBC applet in Control Panel, or if there are two ODBC applets, will be clearly labelled as being 64-bit. To run the 32-bit ODBC Data Source Administrator, which is the one to use if your Informatica client is 32-bit, use this command:

%windir%\syswow64\odbcad32.exe

On UNIX and Linux, the same issue applies: the application's architecture must be the same as the ODBC driver's. You therefore need to check whether your Informatica client is 32-bit or 64-bit and then use an ODBC driver whose architecture is the same. (Note that knowing what architecture your operating system is is not sufficient; you can run a 32-bit application on a 64-bit operating system and so it's Informatica's architecture that you need.)

Another issue for those seeking to use a third party ODBC driver with Informatica is how to set the environment and edit the relevant ODBC configuration files such that Informatica can use the driver. As mentioned, the Informatica distribution includes both an ODBC Driver Manager and a selection of ODBC drivers for various databases. Informatica therefore expects to:

(For the rest of this article, we will use Easysoft ODBC drivers as our example third party ODBC drivers.) Although they are typically used with the unixODBC Driver Manager, Easysoft ODBC drivers are compatible with Informatica's ODBC Driver Manager.

When you attempt to connect to an ODBC data source in Informatica, the application passes the data source name to the ODBC Driver Manager. The ODBC Driver Manager attempts to load the ODBC driver referenced by the data source and passes the data source name to the driver in a connection string. Assuming no other settings are passed in the connection string, the ODBC driver looks up any other connection details it needs in the data source.

To get started, we will install Easysoft's Salesforce ODBC Driver on our Informatica test machine. We accept the default choices during installation and so the ODBC driver is installed in /usr/local/easysoft and a sample data source is installed in /etc/odbc.ini:

$ cd /tmp
$ cd odbc-salesforce-1.0.36-linux-x86-64-ul64
$ su
# ./install

After editing the sample data source so that it specified the Salesforce user name, password and security token, we use isql to test the connection to the data source.

# vi /etc/odbc.ini
[SF_SAMPLE]
Description=Easysoft ODBC-SalesForce Driver
Driver=Easysoft ODBC-SalesForce
uri=https://login.salesforce.com/services/Soap/u/27
user=my_salesforce_user@domain.com
password=my_salesforce_password
token=1234567ABCDEFGHIJK

$ /usr/local/easysoft/unixODBC/bin/isql.sh SF_SAMPLE
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

We now have an installed, licensed, and functioning ODBC driver, but it is not yet available for use in Informatica.

To enable Informatica to find the data source and load the driver, we need to:

You also need to configure the Informatica environment so that the C runtime library is able to load the ODBC driver libraries. Edit the appropriate environment variable for your platform and dynamic linker (LD_LIBRARY_PATH, LIBPATH) and so on). Our Informatica platform is Linux, and so we added the following Salesforce ODBC driver directories to LD_LIBRARY_PATH in the profile file for the Informatica PowerCenter user:

/usr/local/easysoft/sf
/usr/local/easysoft/lib

We then restarted the PowerCenter domain so that Informatica picked up the changes.

The Salesforce connection was then available as a relational target or source (by using the Salesforce data source in a Relational Connection whose type was set to ODBC) in our Informatica workflows.