We're sometimes asked, how can you use the SQL Server
Import and Export Wizard to migrate data from VistaDB 4 to SQL Server 2008?
Using DTSWizard
Take a look at the property for the shortcut from your start menu. If should
point somewhere like "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe".
Launch the DTSWizard. The VistaDB provider will show up in the list of Providers
you can choose in the source database step (only on machines where it is installed
of course).
Select the … in the Data Source row to go and find your database. Everything
else is pretty normal at this point.
Looks easy!
At first it seems quite easy, there is a Data Import Export Wizard for SQL Server,
and it claims to support ADO.NET Providers. But actually using it turns out
to be a pain in the rear as for non Microsoft providers it only supports a mode
that copies one table at a time and you have to write the queries by hand first!
There is a nice pretty “Copy data from one of more tables or views” but it is grayed
out for third parties. Nice.
But it is not so easy for third parties
Sure wish third parties could use that first option, would save you a ton of time.
So you go ahead with the only option you have available and write a quick select
* from sometable. First thing you will notice is that the destination table
has nothing to do with the table you chose, it is [dbo].[Query]. Go ahead
and change that to what you wanted it to be (maybe the table name, crazy thought).
You can change the destination by just selecting it and typing in what you want
(sort of non obvious though). Don’t hit next just yet though, select the Edit
Mappings button near the bottom of the dialog.
[more]
Make sure you select the enable identity insert if you already have identity values
you don’t want to lose! Create destination table is checked by default if
the target database doesn’t have a table already present.
Also keep an eye out for Type changes. For some reason the wizard wants to
convert NText columns to XML! Review your types to ensure they are correct.
Identity columns not mapped
You may also want to edit the SQL to tweak anything you might like (maybe your identity
clause since the wizard isn’t going to create it).
In my example the TicketID is the identity, but the wizard is not going to recreate
the table with that identity present. In my case I needed to add the IDENTITY(2829,1)
in the SQL.
CREATE TABLE [dbo].[Tickets] (
[TicketID] int IDENTITY(2829,1) NOT NULL,
[Title] nvarchar(128) NOT NULL,
[EmailAddress] nvarchar(128),
That identity property is set on the column at the provider level, but the wizard
never checks for it.
Unicode .Net Mapping
Clicking next will present you will a bunch of text basically telling you that there
is no specific mapping file present for VistaDB, so it is going to use the SQL to
Unicode .Net mappings by default. This is in fact just fine.
Save or Run Immediately
At this point if you have a full SQL Server and the SQL Server Integration Services
installed you can save the SSIS package and modify it later. If you are running
SQL Server Express you do not have this option, you can only run it.
Run SSIS results
The run SSIS results look a little strange at first though, if you chose to drop
the table you may have an error like the one below.
The error actually just means that there was no table present to drop. Why
that counts as an error I am not sure (ever hear of try / catch?). But you
can ignore it as long as you see the rows transferred on the copying entry.
One table at a time
Yep, you can basically use this technique to recreate your database, one table at
a time. And you will have no Foreign Keys present because the system never
asks for them.
If you have the full SQL Server installed you can save the dtsx file to your machine
and then go edit them in the Business Intelligence Studio later. If enough
people are interested I will cover that in another blog post.
Manually Build FK’s
The worst side effect of this migration of data is that all FK’s are lost.
Using the full Business Intelligence Studio you can work around this issue though.
It does take quite a bit of time to setup an SSIS package to migrate your database,
but it works in the same basic steps as above.
What about the other tools?
There are other tools that the SSIS uses for full package running and editing.
All of those tools are present in the same directory as the DTSWizard.