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.

More...

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.

More...

We get questions from users asking how they can build client server solutions using VistaDB, or more often just how they can avoid having to share drive letters to share a database.  My traditional answer in the past has been to build a simple data access layer (DAL) and expose it over SOAP or WCF.  Now with ADO.NET Data Services and our Entity Framework (EF) provider you have another choice.

ADO.NET Data Services (Astoria) 

Not familiar with the codename?  “Project Astoria” was released with .Net 3.5 SP1 under the moniker ADO.NET Data Services.  This  services on the surface appears to be about putting generic objects in front of your data and letting it handle protocols for you.  The fact that you can take any EF model (VistaDB included) and put an ADO.Net Data Service around it in a controlled manner is fantastic.  No more need needing to handle the shared drive scenario.  You connect to a service hub, and that hub then applies all the changes and takes care of the ugly handling of edge cases.  This is similar to a feature that allowed direct from within SQL Server, but I think too many DBA’s didn’t want to expose their actual SQL Server.  Push all that exposure out to IIS and you have the same basic functions.

When is it available?

ADO.NET Data Services is .Net 3.5 SP1 technology and is considered production code.  Today you can easily create a service that runs on one local machine and all clients point to it for their data.  This single server allows all others to make queries against it without concern for direct access to the database.

What if the service is unavailable?

ADO.NET Data Services requires online access to the service.  If the service goes down, the clients can’t do much.  There is an offline version of Astoria in the works from Microsoft to allow a local client proxy to build up and sync to the service endpoint.  I have watched a few PDC demos on it and it looks impressive, but it is still in the Alpha stages at the time of this writing.  I tried to play around with it a little and it was almost all still hard coded to SQL Server and SQL CE.

Even without the offline version of Astoria I think you could build a highly reliable system using just the ADO.NET Data Services.  Your client would have to be intelligent enough to know what to do in a failure to connect scenario, but this is no different than a client server database application today.  The client still has to handle what happens when the server goes away.

Take an intranet site and add a client component

A sample scenario we just recently deployed on the intranet could expand to work over the internet.  We could easily build a client timesheet app (copy local or click once) that knows how to get data through the service rather than direct from the database, and perform the data presentation within that client.

More...

The Entity Framework makes it really easy to generate models within Visual Studio 2008.  You must have Visual Studio 2008 SP1 with .Net 3.5 SP1 installed.  You also need VistaDB 4 Build 8 or higher for this video.

I just posted a quick video showing VistaDB 4.0 Build 8 generating an Entity Framework model and adding the model to a console application.

Generate the model

Generating the model is the main point of this video, and it is really quite easy.  Once the model has been generated you need to use it, that is a little more complicated.

Entity Framework Video Tutorial

You can watch the video here, or jump over to YouTube if you want to watch it in HD.  The Entity Framework quick start video is available from the VistaDB Training page on YouTube.

License your Application

After the model is built I show how to add a licenses.licx file to the application (this same step works for any type of application).

More...