Connecting to Informix from SQLSrvr SSIS

Hi, I am trying to connect to an Informix database from MSSqlserver Integration Services. I'm a bit confused and have been trying various approaches and getting all kinds of error messages. For example, using ADO NET Destination .Net Providers\\\\Odbc Data Provider I manage to test the connection correctly and even see the data in the receiving table using the "Preview" button. However, when I run the task I get the following error message: Error: ADO NET Destination has failed to acquire the connection xxx. The connection may have been corrupted. ADO NET Destination (619) failed validation and returned error code 0xC0208452 Additionally, I had to create the ODBC resource using the path %windir%\\\\sysWow64\\\\odbcad32.exe. Otherwise there was a complaint of missmatch of architectures. Is this an SSIS or Informix problem? Or something else? Thanks, Lauri Pietarinen

Hello. I would suggest you to: 1) install Informix Data Server Driver, on the last page of Informix CSDK wizard, choosing it. 2) use an OLEDB direct connection named "Informix OLEDB provider". In order to use it, you must had run a script named coledbp.sql, provided into your CSDK installdir, against Informix sysmaster database. That should provide you a better way to run your SSIS jobs against Informix engines. You could also check Informix online.log file, in order for any specific error message, but that should not work for any sql server side error.... You could also specify your Informix version, and Informix CSDK version installed, and the architecture (I suppose you had installed it into a 64 bits client - windows side, mainly because of syswow64 mentioned issue). Maybe you are using an old client installation, and should be upgraded. Hope this helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: lauri.pietarinen@relational-consulting.com > Subject: Connecting to Informix from SQLSrvr SSIS [32793] > Date: Tue, 25 Mar 2014 13:58:03 -0400 > > Hi, > > I am trying to connect to an Informix database from MSSqlserver Integration > Services. I'm a bit confused and have been trying various approaches and > getting all kinds of error messages. > > For example, using ADO NET Destination .Net Providers\\\\Odbc Data Provider I > manage to test the connection correctly and even see the data in the receiving > table using the "Preview" button. > > However, when I run the task I get the following error message: > > Error: ADO NET Destination has failed to acquire the connection xxx. The > connection may have been corrupted. > ADO NET Destination (619) failed validation and returned error code 0xC0208452 > > Additionally, I had to create the ODBC resource using the path > %windir%\\\\sysWow64\\\\odbcad32.exe. Otherwise there was a complaint of missmatch > of architectures. > > Is this an SSIS or Informix problem? Or something else? > > Thanks, > Lauri Pietarinen > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, thanks for your kind help. My IDS version is 11.7. I have now tried to use the OLEDB-provider (I found "Native OLE DB\\\\IBM Informix OLE DB Provider" in the list of providers in SSIS, so I suppose it has been installed?) - I also ran the script coledbp.sql successfully on "master" I now receive the following error message when testing the connection in SSIS: "Test connection failed because of an error in initializing provider. No error message available, result code: E_FAIL(0x80004005)". I further read in in IBM documentation that a DDL should be registered, but I got an error trying to do that. Thanks, Lauri

Hi, I think you should go through this link, it is very helpful. -- Get started with the IBM Data Server .NET Provider for Informix http://www.ibm.com/developerworks/data/library/techarticle/dm-1007dsnetids/ Additionally, you might be required to make the following changes in your instance: Pre-requisites: =============== 1. Define a new server alias 2. Define a new service in the sqlhosts file using the “drsoctcp” protocol 3. Restart the Informix instance and test it using the “testconn20” application which is being provided by the “IBM Data Server Driver .NET provider” DB Connection Testing Utility Provided by IBM Data Server Driver in Windows: Path: C:\\\\Program Files (x86)\\\\IBM\\\\IBM DATA SERVER DRIVER\\\\bin Additionally, if you want to use the OLE objects, then you will be required to execute the "coledbp.sql" script. These files (coledbp.sql & doledbp.sql) are located in the windows client SDK directory in the etc folder e.g. C:\\\\Program Files (x86)\\\\IBM Informix Client SDK\\\\etc These files needs to be executed in the sysmaster database at the informix instance/server using user "informix". coledbp.sql : to create the objects to support the OLE DB interface doledbp.sql : to drop the objects created by coledbp.sql script to disable the support for OLE DB interface e.g. dbaccess sysmaster coledbp.sql I hope it would be helpful. Regards, Khan

Hi, I managed to get a bit further. It appears that "server" in the SSIS OLE provider means "database@server". I managed to get integers through from an SQLServer table to an Informix table. However, with CHARs I am having problems with code pages. SSIS warns me of the follwing: [OLE DB Destination [634]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used. When I run the SSIS job, new rows are added to testination Informix-table, but the values in char-fields are blank strings. I have tried to change the code pages back and forth but there is always some problem (code page missmatch etc...) Thanks, Lauri

Hi, I got a bit farther with my trials. I can move data in the opposite direction (from Informix to SQLServer), but the other direction produces only nulls or blanks in the receiving informix-table, but a correct amount of rows, any how. I am using the same tables both way. All help appreciated! Lauri

Hi, i changed the receiving column in Informix from char(10) to varchar(10) and after that everything worked perfectly - go figure... Lauri