SSIS solution for access databases

Posted on 2007-10-18
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
    LVL 17

    Expert Comment

    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

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

    Anyone have any thoughts?
    LVL 30

    Expert Comment

    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.
    LVL 30

    Expert Comment

    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

    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

    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

    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

    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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now