Hi,

I have a problem with DTS and the IBM Informix OLEDB driver.

I have a SQL Server 2000 database on a Windows 2000 machine. Informix is installed on a Linux OS on a separate machine. My job was to copy data from a table in the Informix database to a table in the SQL Server database on a periodic basis.

I decided to use the SQL Server DTS to achieve this purpose. I downloaded the latest client sdk (IBM Client SDK 2.81) from the IBM site and installed it on my Windows 2000 machine. I then started the DTS and configured my source database as Informix and my destination database as SQL Server 2000. I used the Informix OLEDB driver to connect to Informix and SQL Server OLEDB driver to connect to SQL Server 2000.

After completing all the steps in DTS (without any error), I saved the package in SQL Server and configured it to run on a periodic basis.

Everything was working fine when out of nowhere in the 3rd week the package started to give errors. Following is the error I got

DTSRun: Executing...
DTSRun OnStart: Drop table [Accounts].[dbo].[emp_accounts] Step DTSRun OnFinish: Drop table [Accounts].[dbo].[emp_accountsl] Step DTSRun OnStart: Create Table [Account].[dbo].[emp_accounts] Step DTSRun OnFinish: Create Table [Accounts].[dbo].[emp_records] Step DTSRun OnStart: Copy Data from Main_Accounts to
[Accounts].[dbo].[emp_accounts] Step

Error string: Catastrophic failure
Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2146959361 (8007FFFF);
Provider Error: 0

This was quite frustrating as nothing was changed in the configuration of either the systems or the databases.

I dropped the package I had created earlier and tried to create another one. But this time as I tried to use the Informix OLEDB driver to connect to Informix, it gave me a "Scheme could not be retrieved" or Catalog error or some thing similar to it. Now it was not connecting to the Informix database through SQL Server DTS.
However, using the same Informix OLEDB driver I am able to access the Informix database through an ASP page and it works fine.

I tried to do some researching and found that the IBM Informix OLEDB driver is not supported by DTS which is very strange as it was working for 3 weeks. In the same documentation it was mentioned that the Merant Informix OLEDB driver works fine with DTS as long as you are importing the data from Informix and exporting it to SQL Server.

What I want to ask is

1. Does the IBM Informix OLEDB driver really work with SQL Server DTS or not ?
2. If it does, can any one point out any thing I may have overlooked ?
3. If it does not, then is Merant Informix OLEDB driver the only one that would fit the bill ?


Please help me to resolve this.

Thanks,

Adeel