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.