I am involved in a project to create a reporting database from an Avaya telephony system (running on an Informix 2000 db) into a SQL 2008R2 database.
Specs. For the Informix 2000 Avaya, I have the OLOD4032.DLL (4.20.04.05) OpenLink Generic 32 bit v4.0. I am using SQL 2008R2, build 10.50.1777EntEdit on 64-bit platform (Win2008R2 EntEdit SvPk1). Using the ODBC Admin 32-bit tool, I was able to configure a DSN with connection success.
When I use the "import data" wizard (SSIS background) in SQL SSMS, I can get it to recognize the 32-bit older driver. I see my dbase, tables, etc., from source, and have created my target tables.
When I try to PREVIEW data, or run the SSIS / Wizard package, I get this error:
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OpenLink ODBC Provider" Hresult: 0x80004005 Description: "[OpenLink][ODBC][Informix Server]Ambiguous column (item_type). (-324)". (SQL Server Import and Export Wizard)
Below is the query:
FROM root.synonyms s
I'm afraid, it could be a bit problematic, there are several differences between both systems, for example, there's no serial data type in MSSQL. I built a similar project, I do dbexport to unl files first, then SSiS imports from flat files. If you do not have to exchange data online, try in this way.