//
you're reading...
SQL Server BI, SSIS

How to Connect to Informix from an SSIS Package

Informix SSIS Configuration

As with many 3rd party products IBM’s Informix OLEDB connection object is less then straight forward when it comes to configuring it to work with SSIS. After some digging I came up with the following way to connect to Informix database through SSIS in a Windows x64 environment. I had to go through stacks of IBM documentation, a lot of articles on the internet, and some trial and error before I reached a working solution. Eventually I was able to achieve the successful connection to an Informix database from an SSIS package (Works for Excel etc. too). Hopefully this post will save you some leg work.

First download the latest version of Informix Client Software Development Kit (32bit) from IBM’s website. It’s a freeware but you have to register on the IBM’s website to download the product. Don’t go for the 64bit versions unless you are working with BIDS 2008R2 or SQL 2012 Data Tools since anything before that (BIDS) is still a 32bit application, and your 64bit providers will not be shown on the providers list. You can download the latest version of client SDK from here.

The next step is to add a path to “C:\Windows\SysWOW64” to the beginning of the path environmental variable before installing the client SDK. Otherwise you will prompt an error message saying add a prefix to path variable. Then install the product including IBM data server driver package.

After installing the client SDK, use Setnet32 tool to configure a default Informix server. You can find Setnet32 application under “All programs>IBM Informix Client SDK”.

Fill Server information and Host information with correct details to your Informix server. You will find the INFORMIXDIR and INFORMIXSERVER environmental variables are filled with values regarding to default server. Then add a new entry to your services file. Service file can be found in “C:\Windows\System32\drivers\etc“.

The format of the service file is clearly mentioned to the beginning of the file. The first entry is the service name then port number and protocol.
After editing the service file, you can create a new Open Data Base Connection (ODBC). Don’t use Data Source Administrator tool in “Program Files>Administrative Tools” instead use “C:\Windows\SysWOW64\odbcad.exe” to add and configure new data source. Weird I know don’t ask me why it works this way but it does

Select IBM INFORMIX ODBC DRIVER and click finish. Then configurations window will pop up. Give any data source name and fill appropriate connection details. Click on apply and test connection.

Almost Done! configuring your environment to connect to Informix. Open BIDS and create a new SSIS project. Go to add new data source, you will find “IBM OLE DB Provider for Informix” is listed under OLE DB providers. Create new connection to the Informix server and have some fun with Data.

The server name should be given in the format of database@servername.

This is CRITICAL! Set the Run64BitRunTime property of your SSIS project to “False” Project>Properties>Debugging

Bon Appetit!

L.Dixon Galler

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

3 thoughts on “How to Connect to Informix from an SSIS Package

  1. Hello Galler,

    Thanks a lot for these steps. It saved a lot of time to me. I was trying to made a connection from SQL server to Informix DB to pull the data. All your steps are working fine until I reach OLE DB source @Data flow task page.

    When I tried to see the tables by selecting table or view @data access mode, I got below error message. Any thoughts on this?

    TITLE: Microsoft Visual Studio
    ——————————

    Could not retrieve the table information for the connection manager ‘v001@ifx1.kvuppala’.
    EIX000: (-111) ISAM error: no record found.

    ——————————
    ADDITIONAL INFORMATION:

    EIX000: (-111) ISAM error: no record found. (Ifxoledbc)

    ——————————
    BUTTONS:

    OK
    ——————————

    Thanks-Krishna

    Posted by Krishna | January 13, 2017, 4:21 pm
  2. Do you know if it is now possible to connect to an Informix db using SSRS Report Builder 3.0 with the graphic-based query designer (as of 2010 only the text-based query designer worked) but I’m hoping, now that 6 + years passed the graphic-based query designer will work as well.

    Posted by Francesca | July 13, 2017, 1:51 pm
  3. Not sure if you can natively connect to Informix via the ssrs report designer, we generally use sprocs and linked servers. It’s probably worth trying as we just migrated to ssrs 2016. We did just upgrade our ssis packages to SQL 2016 in the process we upgraded our Informix drivers to the latest 64 bit version but followed the same process outlined in my post. I will see if there is a way to connect directly to the informix databases via the ssrs report designer.

    Posted by ldgaller | July 15, 2017, 2:07 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: