An earlier post 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.
[more]
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.