Extraction from INFORMIX database using SSIS (Data warehouse ETL)

I have a customer for whom I am building a data warehouse solution using SQL server 2008.

Source data comes in many forms including SQL Server 2000, SQL Server 2005, flat files (csv), Excel and Informix.

SSIS handles connections to older versions of SQL Server perfectly as expected and does a pretty good job with Excel and flat files but INFORMIX is a major pain in the backside and, as expected, neither Microsoft or IBM are particularly helpful.

The data warehouse server in question is Windows Server 64bit and my development machine is Windows 7 professional 64 bit but you cannot just install 64 bit INFORMIX ODBC drivers as Visual Studio (BIDS) is 32 bit.

If you simply install the 64 bit INFORMIX ODBC driver you will see the driver in the ODBC manager but you will not be able to use it. If you try to install the 32 bit driver from the IBM site then it will baulk stating that it is incorrect version for your system. Installation of an older 32 bit driver will work and can be administered via C:\Windows\SysWOW64\odbcad32.exe but you might end up with the “INFORMIX 3.32 32 BIT” driver which just does not work. What you need is the “IBM INFORMIX ODBC DRIVER” from the 32 bit SDK.

Once you have the ODBC driver you will also need to set up the connection and for this you might need to learn about SETNET32, the services file in Windows, the SQLHosts registry entries and you might be just a little confused about how to create the connection in SSIS as it is a little different to all the other types of connection.

Rather than repeat an already excellent article I suggest you follow this link to an excellent step by step installation and setup guide from Dinesh (DSP).

Connecting Informix databases through SSIS

http://dineshpathirana.blogspot.co.uk/2011/08/connecting-informix-databases-through.html

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , , , , , . Bookmark the permalink.

5 Responses to Extraction from INFORMIX database using SSIS (Data warehouse ETL)

  1. Patrick Saeuerl says:

    Hej David, thank you for the post, it helped me a lot.
    Did you experience any problems with extracting character data?

    Kind regards from austria, patrick

  2. Andrea says:

    Hi David,

    I followed the link and I am able to get all the way without error until I go to create the IBM for Infomix OLE DB Provider in BIDS — I get an error saying “Test connection failed because of an error initializing provider. No error message available, result code: DB_E_ERRORSCOCCURED(0x80040E21). Please help. Thanks in advance!!!

    • davidbridge says:

      Sorry to hear that you have the issue Andrea but sadly I am not longer working on this project so have no INFORMIX to connect to now and therefore cannot hope to reproduce the error you are having.

      From what I have read though, the error is reported from ODBC and not from SSIS therefore the issue is definitely in the ODBC config end of things.

      When I was working with Informix I regularly used an application called RazorSQL to look deep in to the DB as you cannot use MS SQL tools. I gather from other posts that I have read that the error number you mentioned can also come from type conversion issues. Using a tool like Razor lets you see the data in the source system so that you can work out what’s weird about it.

      http://www.razorsql.com

      Of course, if your connection is not getting to the source then conversion isn’t the issue. Do you have any kind of trace that you can run at that source?

      Sorry I can’t be much help but that INFORMIX really is a horrible bag of something rather smelly.

      Dave

  3. davidbridge says:

    To answer both Andrea and Patrick and hopefully others with the same issue.

    I did have an issue when using text parameters and receiving large text values.

    My suggestion is bring the data in as raw as you can and then convert it. Don’t try to do to much in the import. When passing data in parameters there seems to be a limit to how long the string is you pass. This is set in the ODBC driver somewhere (but I forgot where). If the data passed is bigger than the buffer it can get truncated and fail.

    So in conclusion, treat everything as the lowest common denominator and maximise compatibility then do the fancy stuff in sql after in staging tables.
    Sadly this is not always possible.

    Dave

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