September 2008

IBM Informix was one our many data sources on this Wells Fargo project, and like everyone else — I was pulling a subset of data for my loan portfolio database.

I was not the first to find that there was a problem with the OLEDB driver when connecting to Informix in SSIS.

But I was the first to discover that the problem was much bigger than anyone realized.

While trying to work out the issue with the metadata and doing some testing, I used Microsoft Access to run some pass-through queries on the data — which is when I noticed something different about the numbers.

The data I was pulling through Access all had decimal places, but they were truncated everywhere throughout our archived tables.

The numbers were showing correctly in Access because ODBC was not corrupting the values like the OLEDB driver was. I hadn’t been there for very long — but 4 developers had been pulling that data for over a year.

So when I told them what was going on, they didn’t believe it.

I sent out 2 or 3 emails with screenshots and everything, and still no response.

I raised the question once again in our weekly meeting and was told “that data has been validated.” I thought to myself . . .

Yeah, that can happen when you’re comparing two wrong datasets against one another ;o)

So I went to my desk and wrote up an even more exhaustive email with more screenshots. And this time the original developer on the project replied . . .

You know what, you’re right — all the decimal places are gone.

He came by and we talked it over to come up with a solution. He was the one who discovered that all we had to do was put a single quote in front of the column name to convert it to a string. That bypassed the driver issue (and of course, we just sent the data into staging tables to be properly converted later in the process).

Problem Solved

They had to go back and pull over 10 years worth of data (which was a pretty time-consuming task given the size of those 300+ column tables).

To be perfectly transparent, I learned a lot more from those guys than they learned from me — they were really smart developers who brought a lot to the table.

But I just have a knack for seeing things that others miss.

For anyone interested in the details of the solution:

I’m a big fan of using system views to generate my SQL (especially when it involves a lot of columns). So I wrote the data type conversion and other logic & syntax using the metadata from system views — returning 300+ columns neatly formatted in my coding convention.

Dropped that result set into a Script Task and we were ready to rock.