My customer uses a tool called IFS (an ERP system), which runs on Oracle (the customer runs 11.2). My job is to extract information from there using Microsoft SQL Server Integration Services (or SSIS). To do so, we're using the Attunity drivers for Oracle (default ODBC is too slow).
I added some new columns to an existing package today, by appending them at the tail of an existing query. Much to my surprise when I then tried joining the new column to a new table, it revealed no results whatsoever. As it turns out, the values in the column were not the values I expected. Some lengthy troubleshooting session later, I found the following.
Most of our IFS objects contain three columns at the end; OBJID, OBJVERSION, and OBJKEY. Something sinister is happening with these columns, though. They are pretty much always selected in the above order:
Querying Oracle yields all valid columns and data:
However, for some reason OBJID is not recognized as a valid column in SSIS, and refuses to show up as a column. Note how the data from Oracle does make its way into the resultset; just not in the right column. The actual data from that column is apparently dismissed altogether (in the above screenshot the column looks blank. I might simply have chosen a bad column to screenshot; it stil proves my point and highlights the core of the issue, though):
When checking the "Column" tab in SSIS, ROWID there is present, but the checkbox is not checked. Checking it manually does nothing.
We import another column called OBJID from SAP, also using SSIS, and that works fine. I am therefore suspecting the issue might be with the Attunity drivers, but cannot seem to figure out exactly what is going on.
I'm hoping someone might have encountered this before, and has a work around.
It's an interesting issue, that had me stumped for a good few hours. I am working around it, so have no immediate need for a resolution; the question is purely to try and satisfy my own curiosity.
I'd be thankful/grateful for any hints :)
