We get this question from customers every so often, how can you use the SQL Server Import and Export Wizard to migrate data from VistaDB 4 to SQL Server 2008?
There is only one real tricky part that is specific to VistaDB 4, the license setup. SQL Server obviously was not built by you (or us), so it has not built in license for VistaDB. But we already thought of that and give you a way to add a Design Time license flag to third party applications through their app.config.
DTSWizard does have an app.config
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". Go to that directory and you will find a DTSWizard.exe.config file that you can edit to include the following lines.
<appSettings>
<add key="VistaDBUseDesignTimeLicense" value="true"/>
</appSettings>
Save the config file and relaunch the DTSWizard, now it has a design time license of VistaDB. That means it can run on any machine where you have a valid developer license installed. 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.
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.
If you are interested in seeing a full migration in the studio let me know.
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 and all have app.config files present as well. Edit each of them to include the same lines and all of them will be able to use the design time license on the machine. If you ever see an error stating that you must have a license to use VistaDB you probably missed an app.config for a tool.
Subscribe