From the last blog post I talked about how to use the SQL Server Import Wizard to get data into SQL Server from VistaDB. You can use that Wizard to build DTS-like packages (They are called dtsx files in SQL Server 2008, but are actually run by SQL Server Integration Services). But that process is a bit cumbersome and only works one table at a time.
You also lose null rows, foreign keys and check constraints. There is a better way, but it involves a LOT more work – build an SSIS package.
SSIS packages are Integration Services Projects
You can build a package through Visual Studio if you have the full SQL Server or SQL Server Developer installed with the SSIS templates. These templates show up as a part of the project types you can choose.
If you do not have Visual Studio on the SQL Server, but have the full SQL Server you get a special version of Visual Studio called SQL Server Business Intelligence Development Studio (I think only Standard and Enterprise come with this).
Launching the shortcut looks very much like Visual Studio Team System. Select Create a new project from the Business Intelligence Projects area of the new project dialog.
Integration Services Project
Choose the Integration Services Project as the template you wish to build. And you will see a new solution like the one pictured.
The SSIS Packages are the output from the DTSWizard in the previous post. You can open one of them to see what steps it took as a result of your choices.
I don’t like the default DTSWizard packages for the above reasons, but also because they don’t use a very good pattern in the SSIS Package.
The first step they call is a Drop Tables task, which as shown yesterday will result in an error if it does not exist because the error clause is not trapped in the package.
The second step is a prepare SQL task that builds the target table (incorrectly in most cases).
And the third step is to do a Data Flow task to migrate the data.
A better approach
A better approach is to build the schema yourself, so the foreign keys, identity fields, etc are all built in advance. I usually run a first step that calls drop on each of the tables I want to migrate, or at least just their data.
These initial tables can be built manually, or from an existing database scripted in SQL Management Studio. A future build of Data Builder will also include SQL scripts that are compatible here as well (it is in the works and this very process how I have been testing them).
Then I manually build Data Flow tasks for each table I want to migrate because you have much finer grain control through Visual Studio.
Chaining the Data Flows Together
The flow diagram you see here is each of my tables being migrated one at a time to the OLE DB datasource for SQL Server. Why OLE DB? For the very specific reason that it works with constraint checking off as an option (required if you have FK’s and Identities in your data).
Data Flow Details
Each of the Data Flows shows (like Tickets) has a detail that breaks down into 3 steps. The first is the loading of a source table entirely (no SQL statement required).
The VDB Database Tickets above may be right clicked and select Edit to see this edit dialog.
Being able to select a table or view avoids nasty surprises with the SQL statement (no need to do a select * on the table). You can drill down to the columns and choose which ones you want to import, what data types they should expose, etc. The interface is really quite powerful, but has one problem.
Unicode – Gotcha
Search around about SSIS packages and you will run into tons of posts where people are having errors in their Data Flow because their types are being confused as unicode. This can come from Excel, flat files, or VistaDB. In any case where a .Net string is returned the packager things it should convert to a DT_WSTR (Wide String) to the OLE DB SQL Server provider.
This leads to problems where the column is taking up “twice” the space you thought it was because a varchar(100) suddenly becomes a 200 byte WSTR entry. Some column types fail altogether at insertion time. Microsoft still states this is by design, they didn’t want to auto promote types for users and would prefer you have to be explicit in your conversion requirements. DTS never required this (SQL 2000) and everything else in SQL Server tends to auto and implictly convert when you don’t want it to! But NOW they decide it is a bad idea.
Data Flow Conversion
The solution is to add a Data Flow Transformation of type Data Conversion to your Data Flow task. You then drag the green output from the VDB4 database and put it into the Data Conversion. In that object (right click edit) you can control the output of each column, including conversion.
Notice the Login column has been changes to an output of Copy of Login with an output of DT_STR rather than the DT_WSTR. This will give the OLE DB provider an ASCII string rather than unicode.
OLE DB Destination
Now the final step is to put the data in the database using the OLE DB Destination object. Drag and drop the green output from the Data Conversion to the OLE DB control, right click edit.
Now we see a dialog that lets us determine where we will put the data in the target server.
You have a number of access modes for the table, and you can select the targeted object directly. Turn off Check Constraints to allow Identity inserts to happen correctly, and no FK validation. Turn on Keep Identity to avoid your old identity values being changes, and Keep Nulls ( the default for the column will be applied when null is found otherwise).
Now those Copy of column names can be visually mapped up to the destination columns to avoid the Unicode problem. You can also avoid all of these steps if your columns are all Nvarchar and Ntext since they don’t have to be migrated.
Build and Run
Once you get all of these in place (one per table, and yes it is a lot of work). You can run and rerun the migration whenever you would like. But if any schema changes, you will have to manually go in and determine what happens to new or old columns.
The messages were not very helpful to finding out why things failed, but they are better then nothing.
Tada! Data in SQL Server!
That’s it! You now have your data in SQL Server. And a way to reproduce the migration whenever you need it.
On my server the migration of the TickeSystem to SQL Server takes about 55 seconds. The same table migrated back to a VistaDB database from SQL Server takes about 7. (I like that)
Quick Video showing editing the data types
Here is a quick video showing how to edit the SSIS package column mappings.
Subscribe