Importing DBF Files Into SQL Server 2008

I’m currently working on a new solution to allow geocoding and data augmentation with demographic data. The demographic data set that we are working with is provided to us as several gigabytes of data separated into logical groupings of .DBF files each with 200+ columns of data and an accompanying data dictionary document in Word format.

I needed to upload all of this data into a new SQL Server 2008 R2 database so that we can do some aggregation and other processing against the data. Unfortunately, SQL Server 2008 R2 does not support a direct import of the data found in the .DBF files. One option is to convert the .DBF files into Excel files. Excel handles the conversion easily. Unfortunately, in my case, the width of the tables is simply too wide for the Microsoft Office Data Access Engine to handle properly. When I attempt to import the resulting Excel files into the database I am greeted with a crash error dialog after a lengthy wait.

The second option, which is the one that I had to go with is to use Microsoft Access to do the conversion. Fortunately, Access will open the .DBF files with no problem. I chose to use the import approach as opposed to the linked tables approach. Due to the size of data that I was dealing with, it was actually faster to load the respective tables into Access, and then use the SQL Server upsizing wizard in Access to push the data into a local SQLExpress instance. Once that had completed, I then detached the database, copied the .MDF and .LDF files to the server and reattached.

The only glitch I’ve had with this process is that for some reason Access will fail to copy data into the SQL Server tables. It’s erratic and I’ve not determined why it does this. There are no errors produced during the export process. It creates the table structure with no problem, but occasionally it will fail to copy the data – even though it runs for several minutes. That’s a battle for another time.

Now I’m off to resume my work on the geocoding functionality that will ultimately pull in this data.

Quick Tip on SQL Server 2008 Database Projects

I just imported the scripts from an existing SQL Server 2008 database. All went well, except that some of the stored procedures made reference to objects from system databases, which the newly imported scripts couldn’t resolve.

Database projects support making references to other databases in a few different ways. One way is via .dbschema files that you can generate from your projects. This approach is a loosely-coupled approach and improves reuse across your projects.

In the case of the master or msdb databases, Microsoft ships .dbschema files for these for this exact purpose. Simply add a database reference to your project and browse to the directory where Microsoft ships these files: [Program Files]\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\Version\DBSchemas, where Version is the version of SQL Server that you are using (such as 2005 or 2008).

For more info on using database references, here’s the link to MSDN:

http://msdn.microsoft.com/en-us/library/bb386242.aspx

New Diggs

It’s been awhile since my last post. The last couple of months have been a whirlwind of activity. Since I last wrote about Continuous Delivery, I’ve switched jobs. The contrasts between the two are innumerable. Went from big corporate to small and personal. I found a place where the project work is genuinely interesting and challenging and above all the people are great to work with.

The beauty of it all is that I get to apply all of the Agile goodness I’ve come to love about my job. In fact, I find all the work on Continuous Delivery to be as important now as ever. It’s not everyday when you find a team that’s beginning greenfield development, who loves the Agile approach (even if they aren’t terribly experienced with it), and who is genuinely concerned about building good software, dependable software, and the pursuit of quality. I have to admit that I was beginning to become skeptical that you could find a team like that – like I was chasing the white whale! But find it I did!

So, now that I’m settled in, I’ll be posting a lot more about the day-to-day challenges of working in a fast-paced, dynamic, and demanding team environment. I’m looking forward to the new challenge and I’m really excited to see the transformation of the new team.

Stay tuned!