SSIS solution for access databases

Posted on 2007-10-18
Medium Priority
Last Modified: 2013-11-30

I am new to SQL 2005 Integration Services.  I had some experience with DTS packages but not much...  I'm going through a few

SSIS tutorials right now, but I'm sure that they will not be able to provide me some direction for the following business


Bachground Info:
1) We have about 100 Access Databases.  There is one for each of our "customers".
2) The databases each contain multiple tables.
3) The schema is roughly the same for each database.  They are basically copies with different data.
4) The number of databases changes periodically when we add new customers, or split customers out into several databases

(This would cause one database to be deleted and several more created)

The problem:
My current solution is to first use a VB.NET application to "read" a directory containing all of the mdb files and create

linked servers for each of the Access DBs.  Next I use one giant SQL script file that performs import operations by

referencing each linked sever. To make matters worse, I query for a list of the linked severs then create and "EXEC" dynamic

sQL in a cursor loop. This is unacceptably slow.

I hope that someone can offer a better solution.  In any case, I plan to compile as much code as possible in stored

procedures and bundle the whole thing using SSIS.

I think that I need to start by Somehow Opening all of the Access Databases in a given directory and read them into a temp

table.  Can someone please help with this and also offer overall thoughts?

Here is a small segment of my script that deals with the Access DBs

-- Declare Variables
DECLARE @AccessServerName varchar(50)
DECLARE @tblName varchar(26)

-- Create a Temp Table to store each Access DB's Table names
   TABLE_CAT sysname null,
   TABLE_SCHEM sysname null,
   TABLE_NAME sysname,
   TABLE_TYPE nvarchar(100),
   REMARKS nvarchar(100) null

--Loop Through Linked Servers
      SELECT name FROM sys.servers


      PRINT @AccessServerName

      -- Loop through the tables in this Access DB
      -- Fill Temporary Meta Table with Server's tables
      Execute ('INSERT ExMetaTable Execute [sys].[sp_tables_ex] @AccessServerName = [' + @ServerName + '], @table_type =


      --Loop through each table
            SELECT Table_Name FROM ExMetaTable

      OPEN T1
      FETCH NEXT FROM T1 Into @tblName

      While @@FETCH_STATUS = 0
            --Enter the DataFeed information
            Execute ('INSERT INTO MyNewTable (Val1, Val2)
                              SELECT NewID(), AccessTable.OldCol
                              FROM [' + @ServerName + ']...[' + @tblName + ']'
            FETCH NEXT FROM T1 INTO @tblName

      -- Tidy up and Clear the temp table
      Close T1
      Deallocate T1
      DELETE ExMetaTable

      -- Get the next Access server
      FETCH NEXT FROM C1 INTO @AccessServerName
Question by:a222493
  • 5
  • 5
LVL 17

Expert Comment

by:Chris Mangus
ID: 20101683
I can't remember the name of it off the top of my head, but, SSIS has a task that will do through all the files in a folder.  Within that task, you could put your import SQL statement.

Author Comment

ID: 20103113
Thanks cmangus,
I'm really more interested in some design suggestions.  

Anyone have any thoughts?
LVL 30

Expert Comment

ID: 20105358
cmangus is right, the task is called for each loop. You set it up to iterate through all the files in a folder. Then you assign the filename to the connection inside the loop. Inside the loop you have one or more data flow tasks (like a datapump).

The result is that all of the access files will have one or more tables imported.

A very basic introduction to this concept is found here at this help reference:


I used DTS + stored procedures a lot in the past but in my latest project I have used SSIS alone (no SP's) and it works very quickly and efficiently, and there is no T-SQL to go scratching through.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 30

Expert Comment

ID: 20105380
Actually, you've explained your solution but not your problem... do you want to refresh these db's every day? Do you want to keep clients whose MDB's have disappeared?

Author Comment

ID: 20110883
Thanks for the help nmcdermaid.  That pointed me in the right direction.

To answer your questions:
 1) Yes, I want to refresh the database every night
2) I want to delete data from SQL where the mdbs have been deleted, and add where they have been added.

I now have a package designed that will itterate through a directory and copy tables from each Access DB into corrasponding tables in SQL.

Current Problem:
1) I have no way of knowing which database, data came from.  I would like to insert the MDB filename into the SQL table through a variable for each row imported.

2) Not all tables exist in each database.  How can I test for the existance of a table before attempting to import?

LVL 30

Expert Comment

ID: 20113969
1) From memory, I think the for each loop puts the filename into a variable for you.. therefore

You can use a derived columns task within the data flow to add the variable to the data stream.
Or you can put a execute sql task inside the loop and just insert it once. You type something like this:

insert into table1 (field)
select ?

and assign the variable (which contains the MDB file name) to the input parameter. If your variable is called MDBFileName, then the syntax for it is @[User::MDBFileName]

2) In SQL 2000 I would use select COUNT(*) from sysobjects where [name] = ?, and again parameter in the table name. THe query will return 1 if the table exists and 0 if it doesn't. MS Access has a similar table, something like:

SELECT COUNT(*) FROM MSysObjects WHERE Name = (explicit table name or parameter placeholder) AND Type = 1

Thats a quick explanation, post back for more info.

Author Comment

ID: 20138762
Thanks again nmcdermaid!

Now I have the loop working, reading in access databases and inserting the database name using a derived column.

Next Problem (I can start another thread and award points if you like.  Just let me know)
Not all of the databases have the same tables, so I would like to error out gracefully if the table is not found and keep going.  How do I handle the error of a table not found?

The data flow is:
1) OLE Access Source (using varFileName for the Server attribute) This also attempts to open the table specified
2) Derived column
3) OLE  Destination (SQL Server)

The tables that I am interested in are Hold01, Hold02, Hold03...Hold07 (The numbers represent years)  Some of the client databases don't have those tables, so I want to "Skip" them.  Problem is that when the dataflow can't open one of the tables, then the process stops.  

How can I lkeep going?


Author Comment

ID: 20142065
Any thoughts on the above problem? i.e. I need to keep going if one of my tables is not there...
LVL 30

Expert Comment

ID: 20143460
Running this SQL on the current MS Access db should list all the tables that you are interested in. (Test it directly in an MS Access db first)

SELECT Name As TableName as TableExists
FROM MSysObjects WHERE Name LIKE 'Hold*' AND Type = 1

So you need to populate a recordset variable with the result of that SQL. Then you need to use a for each ADO task to enumerate therough the rows in that variable.

There is probably a way to disregard a missing table, but I'm not too sure... maybe something to do with 'forced execution value'

Author Comment

ID: 20300846
I thought that I had this, but I wasn't sure so I left this thread open.  Good thing....

I have two for loops woking, but I'm not sure how to transfer the data...
Below is my pseudo code:

Setup an OLEDB connection so that the connection string uses my @Usr:CurrentMDBFile

Foreach mdb file in my direcotry
     SQL Statement to query for all tables like 'Hold%'
     Foreach Table in tablelist returned from query

     'This is where I'm stuck, I'm not sure how to transfer the data.
     1)  How do I configure a source and destination when I don't know the tables?
     I hoped there would be an expression that exposed a property like "Table" that I could set using a variable...
LVL 30

Accepted Solution

nmcdermaid earned 2000 total points
ID: 20309836
This is from memory.... YOU drop a single data flow task in to the middle. You set it up for a dummy table. Then go back to the control flow tab (not the data flow) and find 'expressions' in the properties. Assign your variables in there.

I ~think~ you just pick a single table to set it up then you use expresssions to assign your variables.

Have a go and if you can't sort it let me know and I'll have a look.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question