Can Microsoft SQL Server and Informix DB2 environments integrate together? The answer is YES!! I have received an increasing amount of questions concerning wanting to cross platform ETL development work between the two. Driven from these questions, I want to dig deeper into regards to manipulating data between Microsoft SQL Server and Informix DB2.

Recently, I have been asked to load data to Informix DB2 using SSIS which is the focus of my topic. When I was tasked with this request, I did some research and started to develop a solution. However, I ran into some common issues that had unanswered questions in regards to writing via Informix ODBC with SSIS out on the internet. Unfortunately, to this day, I have not seen an actual step- by- step blog about this topic based on my own personal searches. With that being said, I decided to blog about it myself.

Let’s start with the basic information first. What do you need to successfully use Informix with your SQL Server environment?

You should know, at minimum, the following:

• What versions of the driver you have
• What version of SQL Server is installed on your server
• What the version of your operating system is

The version of the driver can cause unforeseen issues when trying to load into Informix via SSIS. Check how your ODBC driver is registered. You can do this by simply checking both 32 bit and 64 bit ODBC Data Source Administrator. Here are the commands for 32 bit and 64 bit respectively:

32 Bit: C:WindowsSysWOW64odbcad32.exe

64 Bit: C:Windowssystem32odbcad32.exe

As you can see, I do have both registered in my current environment:

32 Bit

64 Bit

This is a common issue I have seen between the two. No matter if you have SQL Server 32 Bit or 64 Bit, BIDS is a 32 bit platform application, and the runtime of BIDS needs to be set to reflect this. This is done in the solution properties.

In the properties, you click the debugging option and set Run64BitRunTime from True to False.

Now, you are ready to set up your connections and build your package. In your connection manager, select where your source data is coming from. For my example, it’s going to be SQL Server, so I need an OLE DB connection. The destination I will use is an ADO.NET connection manager.

Here is the little piece that took a while to figure out. Your connection string within your ADO.NET connection manager needs to have “delimident=y” as an argument within the connection string.

Now, my connection string reads as follows:

Dsn=INFORMIX ODBC;Driver={ifxoledbc};delimident=y

Notice that I do not have my UID or password passed in through the connection string because they are already stored on my server when I set them up in my Data Source ODBC Administrator.

From here, I am going to simply set up my Dataflow with a source and destination using the connection managers that I have created and map all of my columns.

That’s it! Now, all you have to do is run it and test it.

I have just written 27 records to Informix DB2 via SSIS using the Informix ODBC driver provided by IBM! Extracting, Transforming, and Loading data (ETL) sometimes requires outside drivers and connection managers which require us to learn new thing, and we are learning new things every day in the development world. I hope that you found my blog informative and that it helps others reduce the search for writing to Informix via SSIS. Stay tuned for my next blog post in the next few weeks.