Using Informix dbaccess in SSIS to export data to be imported into SQL Server

0
votes

I currently have a SSIS package similar to the one detailed here which loops over a list of tables that are in a IBM DB2 database, exports the table contents into text files using the "Data Transfer From iSeries Server" for each table and then imports them into SQL Server using a bulk insert task.

We are moving to a new Informix 11.50 fc7w3 database and would like to use a SSIS package of a similar nature to export tables to SQL Server.

I believe I need to execute the dbaccess with the appropriate .sql files containing an UNLOAD statement.

Firstly, is it going to be possible to execute dbaccess via SSIS with an argument of the .sql file containing the UNLOAD statement.

Secondly, what would be the arguments or command line prompt I would need to execute.

I am trying to test this on a server which has the IBM Informix Client installed and the dbaccess tool though when I run the tool I receive the following error:

ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment.

Any suggestions on how I can rectify the issue above? I have little to no knowledge of administering an Informix database/server.

The server I am trying to execute the dbaccess tool on is Windows Server 2008 R2 with SQL Server 2008 R2.

1
sqlssisinformix
You shouldn't be using a 'new 11.50` Informix database; it is the trailing edge version (the oldest still supported version). There might be reasons for using it, but it should not be used for a new system. Informix 12.10 would be best; 11.70 more or less acceptable. - Jonathan Leffler
Its new to us considering the previous system is some 20+ years old. I couldn't agree with you more but I do not have a say in which version we will be running. - mheptinstall

1 Answers

0
votes

In order to do it from SSIS you might execute some java/python or whatever code that connects to Informix and returns data to your client file system by issuing regular select statement. Unload command will just export data into Informix server local file system. You can call Informix stored procedure that may also use unload command. In this case you may access your output files with NFS/Samba and etc. to read them by SSIS.