A new task

Recently I was given a little task about extracting data from a SQL Server 2005 to a Microsoft Access database. The objective was to drop an Access file on a shared filesystem location within the company.

I chose to work with the SQL Server Integration Services (SSIS), which was built for such tasks. You can find some general info about SSIS on the Microsoft SSIS homepage.

SQL Server integration Services is an available option when you’re installing SQL Server 2005 (Standard, Developer or Enterprise Edition).

The MS Access database

I had to create an empty Access database. This database contains the proper tables, so I can store my data.

The first pain point was the SQL Unique Identifier, or Guid, datatype. Microsoft Access doesn’t support Guid datatypes. So all identifiers in the Access database are varchar(36) strings.

The Dtsx Package

Step 1: Script Task

The first thing we need, is a clean target MS Access database. The script task (which is written in VB.NET) will create a copy of the Access database I created before.

Step 2: Sequence Container

Now that an empty Access database is available, I can start getting the data from the SQL server instance and map it to my tables in Access. This is done with some “Data Flow Task” objects. Within a “Data Flow Task”, I have an “OLE DB Source” that goes through a “Data Conversion” object and ultimately to an “OLE DB Destination”.

Step 3: Script Task again

When all data is pumped into the Access database. The script will move it to the shared filesystem location

The encountered problems

Guids, again…

As I told you earlier, access doens’t support Guid datatypes. But apparently things go wrong before my data is converted in the “Data Conversion” object to a string. So I had to update ALL my queries and cast every Guid to a varchar(36).

Some queries were very complex. I overlooked a Guid, because it only occured in the forth union. Which resulted in the loss of an hour in my life.

Avoid Decimals in Access…

… because they don’t work! I’ve spent almost two hours in trying different sorts of type cast. Within my SQL statement and the “Data Conversion” object, with no luck. I always got a “value overflow” exception or similar others. Being fresh out of new ideas, I went to my Access database, changed the datatype of an overflowing field from “Decimal” to “Double Precision”. Suddenly, without any casts or other tricks, the field was successfully processed. So I had to convert ALL “Decimal” datatypes in Access to “Double Precision” datatypes.


What seemed to be an easy and quick to do task, took me a little longer and was more frustrating than ever anticipated.

I didn’t like Microsoft Access before, let’s stay polite and just say I still don’t like it now.