Import from INFORMIX (Cannot get the supported data types)

Hi!

I'm trying to import data from an Informix Database to my SQL Server 2005 database with the import/export wizard.
I have an ODBC-connection that works, Access fetches data with no errors. From the wizard I can only choose to use the IBM Informix OLE DB Provider as datasource. As fare as I know the OLE DB Provider is using settings from the ODBC-driver?

In SQL Server 2000 there is a posibillity to choose Other(ODBC provider) as datasource which works against this database. Any ideas how i can use an ODBC-datasource?
If I try to use the IBM Informix OLE DB Provider I get an error message after the first step of the wizard:

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=uid;Data Source=db@.server".

ADDITIONAL INFORMATION:

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Anyone have any experience with this problem? I can't find any information that will solve this, but I have seen several questions about this.

I would be very grateful for some help!

Best Regards

Hello

Did you ever find the solution to this problem? I'm having similar errors and don't know what if wrong.

Thanks

|||Hi!

I could not find any solution. I had to uninstall SQL server 2005 and use SQL server 2000.

I think the problem has something with the SQL 2005 server's ODBC support. I read in a forum that Microsoft spent their time on OLE-DB, and that the OLE-DB driver from Informix has some problems. But i gave up trying to figure it out.

I'm sorry!

Trond U|||

Thanks for the information. That really bites. This is a huge disappointment. I guess I will have to find a machine with SQL Server 2000 installed on it. I don't have the option of uninstalling my SQL Server 2005.

Thank you for getting back to me,

Matt

Import from INFORMIX (Cannot get the supported data types)

Hi!

I'm trying to import data from an Informix Database to my SQL Server 2005 database with the import/export wizard.
I have an ODBC-connection that works, Access fetches data with no errors. From the wizard I can only choose to use the IBM Informix OLE DB Provider as datasource. As fare as I know the OLE DB Provider is using settings from the ODBC-driver?

In SQL Server 2000 there is a posibillity to choose Other(ODBC provider) as datasource which works against this database. Any ideas how i can use an ODBC-datasource?
If I try to use the IBM Informix OLE DB Provider I get an error message after the first step of the wizard:

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=uid;Data Source=db@.server".

ADDITIONAL INFORMATION:

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Anyone have any experience with this problem? I can't find any information that will solve this, but I have seen several questions about this.

I would be very grateful for some help!

Best Regards

Hello

Did you ever find the solution to this problem? I'm having similar errors and don't know what if wrong.

Thanks

|||Hi!

I could not find any solution. I had to uninstall SQL server 2005 and use SQL server 2000.

I think the problem has something with the SQL 2005 server's ODBC support. I read in a forum that Microsoft spent their time on OLE-DB, and that the OLE-DB driver from Informix has some problems. But i gave up trying to figure it out.

I'm sorry!

Trond U|||

Thanks for the information. That really bites. This is a huge disappointment. I guess I will have to find a machine with SQL Server 2000 installed on it. I don't have the option of uninstalling my SQL Server 2005.

Thank you for getting back to me,

Matt

Import Export Wizard - ODBC to SQL

Hi all,

we have recently purchased a server with sql 2005. One of the things we are looking to do is pull our entire informix database over onto the sql box. I have tried using the import/export wizard however when it comes to the copying I the option to copy all tables is greyed out. I have been able to do this sql to sql but not when i select the source as odbc and destination as sql.

I need to try and resolve this quite quickly and any advice/help would be appreciated. Please bear in mind that im relatively new to this stuff. I have had a look at other posts, and am getting the impression that this cannot be done simply. My initial understanding was that this could be done using the DTS in sql 2000 quite simply, but was also told that it should be even easier in 2005..

Well appreciate any help

Ian

Unfortunately, your observations are right.

We do not natively support ODBC providers in SSIS. We do support ADO .NET provider for ODBC, but ADO .NET has somewhat complicated story with fetching table/column metadata generically, and we ran out of time to focus on that. That is the reason the wizard cannot offer the table selection page when ADO .NET providers are used (except for SQL Server one). We plan to have much more focus on this in the next version of the product.

For now, your best options are either to use the DTS wizard or to programmatically build SSIS packages (similar to the one the wizard produces for a single table) on the fly.

HTH. Thanks.

|||

Hi Bob,

thanks for the quick response. It will save me hours scouring forums and such for clarification. Following your response i cannot wait for an updated version to be released (hoping against hope that this will be a service pack, and not a requirement for me to purchase and upgrade)

One thing you did mention however was to use the DTS wizard.. can you elaborate on this.. how do i run this/where do i find it.. my understanding obvioulsy incorrect was that SSIS replaced DTS

Thanks once again

Ian

|||

Hi Ian,

I ran into the exact same problem: when trying to migrate informix table to sql server, the Copy Tables / Views option is greyed out. I am struggeling on a 'table-by-table' basis; you might try oledb with a so called linked server (create a linked server through oledb with the ifxoledbc driver that is provided in the Informix Client SDK). You can write queries that allow table migrations, but it is a lot of work when migrating hundreds of tables.....

Regards,

|||

The options mentioned in Bob's post was to either use the DTS wizard you had with SQL 2K and then upg your target to SQL 2k5.

Unless this is not a one time exercise - in that case you could programmatically create an SSIS package similar to a DTS package that migrates one table and repeat it for the whole set of source tables.

Import Export Wizard - ODBC to SQL

Hi all,

we have recently purchased a server with sql 2005. One of the things we are looking to do is pull our entire informix database over onto the sql box. I have tried using the import/export wizard however when it comes to the copying I the option to copy all tables is greyed out. I have been able to do this sql to sql but not when i select the source as odbc and destination as sql.

I need to try and resolve this quite quickly and any advice/help would be appreciated. Please bear in mind that im relatively new to this stuff. I have had a look at other posts, and am getting the impression that this cannot be done simply. My initial understanding was that this could be done using the DTS in sql 2000 quite simply, but was also told that it should be even easier in 2005..

Well appreciate any help

Ian

Unfortunately, your observations are right.

We do not natively support ODBC providers in SSIS. We do support ADO .NET provider for ODBC, but ADO .NET has somewhat complicated story with fetching table/column metadata generically, and we ran out of time to focus on that. That is the reason the wizard cannot offer the table selection page when ADO .NET providers are used (except for SQL Server one). We plan to have much more focus on this in the next version of the product.

For now, your best options are either to use the DTS wizard or to programmatically build SSIS packages (similar to the one the wizard produces for a single table) on the fly.

HTH. Thanks.

|||

Hi Bob,

thanks for the quick response. It will save me hours scouring forums and such for clarification. Following your response i cannot wait for an updated version to be released (hoping against hope that this will be a service pack, and not a requirement for me to purchase and upgrade)

One thing you did mention however was to use the DTS wizard.. can you elaborate on this.. how do i run this/where do i find it.. my understanding obvioulsy incorrect was that SSIS replaced DTS

Thanks once again

Ian

|||

Hi Ian,

I ran into the exact same problem: when trying to migrate informix table to sql server, the Copy Tables / Views option is greyed out. I am struggeling on a 'table-by-table' basis; you might try oledb with a so called linked server (create a linked server through oledb with the ifxoledbc driver that is provided in the Informix Client SDK). You can write queries that allow table migrations, but it is a lot of work when migrating hundreds of tables.....

Regards,

|||

The options mentioned in Bob's post was to either use the DTS wizard you had with SQL 2K and then upg your target to SQL 2k5.

Unless this is not a one time exercise - in that case you could programmatically create an SSIS package similar to a DTS package that migrates one table and repeat it for the whole set of source tables.