Extracting Informix data into SQL Server 2008

Question

All,

Please can someone show me a noob guide to connecting to Informix via sql server 2008 and the best way to extract data for the following senario.  Thanks in advance

I have been asked to extract some data from informix into a SQL table/view.  The new table should be updated roughly every 5 minutes.  I have no clue what the best procedure would be to extract the data into sql server.  I have done some research and my options are to either use ssis or a views\Stored procedure using a linked server object.  For both these options, I have no clue how to establish the connection to the informix database. 

Please can someone assist. Preferably the most simple solution.

Kind regards

Answer 1

Hello,

Please read the following resources:

MigrateInformixtoSQLServer2008.docx on http://www.microsoft.com/en-us/download/details.aspx?id=24662

http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/15929802-01fd-4549-9728-dd3bd33214eb/#96780089-a1c1-40d9-b8d7-91d015635d94

http://www-01.ibm.com/support/docview.wss?uid=swg21195578 (Example)

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Thanks for the information Alberto.  It is still confusing what ole db to download looking at the example.  Currently I connect to the Informix database via Server Studio R8.5a from AGS.  My connection details are shown below.  Can you suggest what OLE DB provider I need and how I would configure it based on the information below.  Thank you 

Informix Server details: -

Server = ol_ukdb2

Host = ukdb2

User = informix

Version = IBM Informix Dynamic Server Version 11.50.FC2

SSL = No

Edit:  I have some more information for the connection string : -

Provider=Ifxoledbc.2;Password=Sentinel;Persist Security Info=True;User ID=SentinelUser;

Data Source = isent@ol_ukdb2

Extended Properties="DB_LOCALE=en_us.819"

Also,  I have installed the SDK and I can see the OLE DB for IBM Informix.  Do you know how I can use the top string to create the linked server connection?

Answer 3

Hello,

You may still find the driver on the IBM Informix Client SDK. Try to download a trial version.

http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl

Create an ODBC DSN using control panel and provide the name of the ODBC as the Data Source when you create the linked server. The following article may help:

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

Thanks Alberto,  I will try that. 

Here is something I tried earlier: -

Within Setnet32, environment tab.  I set the DB_LOCALE = en_us.819.  In the server information tab, I set the server to ol_ukdb2, Host ukdb2, Protocol name = olsoctcp, Service Name = turbo.  In the Host information tab, I set current Host to ukdb2, Username = SentinelUser And entered the password.

Now when I try to create a connection in SSIS using OLE DB\IBM Informix I get the following error when trying to test connection: -

"Test connection failed because of an error in initializing provider.  No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

I can successfully create a connection using a udl file I created so I now the connection exists: -

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Ifxoledbc.2;Password=Sentinel;Persist Security Info=True;User ID=SentinelUser;Data Source=isent98ol_ukdb2

Extended Properties="DB_LOCALE=en_us.819"

Answer 5

Hello,

I don’t have experience migrating data from Informix databases using SSIS. I would try to perform the migration using a linked server and ODBC.

Another suggestion is to ask for help on the Integration Services forum. Maybe they can provide you a workaround,

The fact that you are getting a successful connection from the UDL is good news, both servers can now communicate between them, is just a matter of finding the best method to migrate the data.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 6

Thanks again Alberto.  I have created an ODBC driver which connects successfully. 

The ODBC is called "Sentinel" 

The following has been completed in the connection tab: -

ServerName = ol_ukdb2

Host Name = ukdb2

Service = turbo

Protocol = olsoctcp

Database Name = isent98

User Id = Sentinel

And then I have entered a password.

I can successfully test the connection. How do I create a linked server based on this information? Can you send me the sp_addlinkedserver code for the info above?  Also, how do I reference the linked server in my sql query (based on the information above? 

Thanks.  Sorry I'm such a noob.

Edit: I think I have created a Linked server which connects successfully.  Do you know how to reference it a sql statement? Thanks

Answer 7

Hello, I know you are doing something is not common and not well documented on the Web.

Below is how to add the linked server using T-sql. Change the provider, the servername (I wrote InformixSrv), the rmtuser and rmtpassword parameters.

----------------------------------------------------------------------------- /

EXEC master.dbo.sp_addlinkedserver @server = N'InformixSrv', @srvproduct=N'InformixSrv - Migration', @provider=N'MSDAORA', @datasrc=N'Sentinel'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'InformixSrv',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteUser',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'InformixSrv', @optname=N'use remote collation', @optvalue=N'true'