SQL Server 2008 import schema and data from ADO.NET Provider?

Question

I am trying to figure how to get SQL Server 2008 to import data using the schema from an ADO.NET Provider. You can use the Database Import wizard and choose any ADO.NET Provider (I am using VistaDB in this case). But once you go to the actual import the Copy Data from one or more tables is grayed out. You can only write queries. I want to copy the entire database schema and data.

I found references in the online help that only Microsoft providers can use this by default and that you have to build a custom descriptor for third parties. The help then points to :\Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors for the directory to look. Looking through the XML file is looks like it wants SQL queries for all the schema and definition loads. Why can't it load them through the GetSchema() call that all ADO.NET Providers have to implement? That doesn't make any sense to me.

Then I found that you can get all the schema in a DTS package in SQL Server from any ADO.NET Provider. The last DTS package I wrote was about 10 years ago. Looking at the 2008 version I am totally lost. How do you get a DTS package to load the entire database schema and import it in SQL Server today?

Or, am I over thinking this and there is an easier way to do it?

Answer 1

Ok, I eventually created two blog posts about this subject for migration specifically to SQL Server from VistaDB using SQL Server tools. It is non-intuitive and pretty much you have to use SSIS, there is no other way.

Using SQL Server Import and Export Wizard with VistaDB - Mostly information on using the default wizard, but I don't recommend it. It will only do 1 table at a time and it painful to setup. You also can't run this repeatedly with anything other than SQL Server Standard or higher (express can't do it).

Using SQL Server Integration Services to Migrate VistaDB 4 Data - Most of what's in this post applies to any ADO.NET Provider. I found parts of the information from mySQL experts, DB2, and others. I just put it all together for VistaDB users.

Interesting information is that it took me 3.5 hours to build the migration for an 11 table database through SSIS. The process is very painful and has a lot of click steps that drive me nuts (use the advanced edit, not the normal edit). Once I got the entire SSIS package setup it took about 2 minutes to run. And then I could (in theory) re-run it whenever I wanted. But in my case I only wanted to move the data and structure one time. A lot of time for setup.

Then I wrote a simple app to walk all my tables and just spit out SQL statements that I could run from sqlcmd. Writing that app took about 1.5 hours, and executed in about 10 seconds. But then sqlcmd took almost an hour to run all those statements one at a time.

I may build a reverse of our Data Migration Wizard (it copies from SQL Server into VistaDB). You can migrate a database in as few as 7 clicks, and it takes seconds to run. All the data access is through the GetSchema() calls to the ADO.NET Provider.