How to Configure Microsoft SQL Server ODBC connection for Informatica PowerCenter installed on Unix

Install and configure DataDirect ODBC on UNIX

1. Install the PowerCenter Services which include the DataDirect ODBC drivers for UNIX.

2. Set the ODBCHOME environment variable to the location where the drivers have been installed.
ODBCHOME=/informatica/odbc42

3. Set the library path variable to include $ODBCHOME/lib: LIBPATH=$ODBCHOME/lib
The library path will vary by operating system:

• Solaris – LD_LIBRARY_PATH
• HP-UX – SH_LIBPATH
• AIX – LIBPATH

4. Copy the odbc.ini file to the location where you would like this file to be used. The Informatica user’s home directory is used generally.
cp $ODBCHOME/odbc.ini $HOME/.odbc.ini

5. Set the ODBCINI environment variable in the .profile to the location of the final version of .odbc.ini :
ODBCINI=$HOME/.odbc.ini

6. Add the ODBC/bin directory to the PATH environment variable:
PATH=$ODBCHOME/bin

7. Run a test of the ODBC driver using ivtestlib and specify the ODBC driver file you will be using.
$ODBCHOME/bin/ivtestlib $ODBCHOME/lib/DWmsss19.so
The library name can be found in the odbc.ini file. If there is an error then check the environment variables above.

8. Configure the .odbc.ini file specified by the $ODBCINI environment variable.

9. Add an entry for the ODBC data source under the [ODBC Data Sources] section and configure the data source as follows:

[ODBC Data Sources]
MY_MSSQLSERVER_ODBC_SOURCE=
[MY_SQLSERVER_ODBC_SOURCE]
Driver=
Description=DataDirect 5.1 SQL Server Wire Protocol
Database=
LogonID=
Password=
Address=,
QuoteId=No
AnsiNPW=No
ApplicationsUsingThreads=1

The driver name and location depends on the version of DataDirect ODBC drivers being used, the operating system they are installed on and the directory they are installed in.

Example:

If the SQL Server database server is sql_server_1 running on the (default) port of 1433 enter the following:

[ODBC Data Sources]
SQLSERVER_1=DataDirect 5.10 SQL Server Wire Protocol

[SQLSERVER_1]
Driver=/apps/rdbms/odbc410/lib/DWmsss18.so
Description=DataDirect 5.10 SQL Server Wire Protocol
Database=Master
LogonId=target_user
Password=target_pw
Address=sql_server_1,1433
QuotedId=No
AnsiNPW=No

SQL Server Named Instance
Use the syntax below for Address to connect to a Microsoft SQL Server Named Instance (for dynamic ports):
Address=\
If the instance name is INFASQL2K5 and is on the server tswin2003 enter the following for Address:
Address=tswin2003\INFASQL2K5

10. Verify that the last entry in the odbc.ini is InstallDir and set it to the ODBC installation directory ($ODBCHOME):
InstallDir=/usr/odbc

PowerCenter Client (Windows)

To use these ODBC connections in PowerCenter sessions it is required to configure ODBC relational connections in the Workflow Manager PowerCenter Client (Windows).

1. In Workflow Manager add a relational connection.
2. Make the connection type ODBC (do not use Microsoft SQL Server).
3. Set the connection properties as follows:

• Connect String: MY_MSSQLSERVER_ODBC_SOURCE
• User Name:
• Password:

 

Example:

4. This relational connection can be used in Informatica PowerCenter sessions.

Thanks,
~KKT~