How to import multiple Access databases into SQL S

Question

I have a folder with more than 300 Access databases (written by a program over which I have no control). They all have the same structure, just a single table. I am importing the data into a table in SQL Server (2005). Using the import wizard works great, but it only works with one Access db at a time.

I have searched and searched for a way to do this, and I thought I was on to something using a Data Flow Task inside a ForEach Loop Container. However, I only see a way to do this with Excel files or flat files as sources.

Any help is greatly appreciated.

Answer 1

What you need to do is this:

1.- Create 2 variables inside you package, one called "current_file" and another called "loading_location" (or the names you prefer), both with scope over the package, Data Type String, for current_file keep the value empty, on loading_location put the route for your folder with the Access databases.

2.- Add a Foreach Loop Container, inside that go to Collection, select Expressions, and add a new Expression. On property select Directory, and in Expression select the loading_location variable you just created. Go back to collection again and in Files put this: *.mdb

Keep all other options the same. Then go to Variable Mappings and in Variable select the current_file variable.

3.- Create a new OLE DB Connection. On Provider select "Microsoft Jet 4.0 OLE DB Provider", on Database file name select any of your access DB (this will change later don't worry).

4.- Create a Data Flow Task inside the Foreach Loop Container, add an OLE DB Source inside that Data Flow Task. Open the OLE DB Source, in connection manager, select the connection you just created, in Data Access mode select "Table or view" and select the table for the DB.

5.- Add an OLE DB Destination, select the corresponding connection for the destination database and select the table where you will put the data for the Access Databases.

Now we will make the changes for this to go through every Access Database..

6.- Select the connection you created for the Access DB, go to the Properties window and take note over the ConnectionString value, in my case it was:

Data Source="MyAccessDBFile";Provider=Microsoft.Jet.OLEDB.4.0;

You may have additional stuff according to the permission over the DB. Now go to the Expression attribute, expand it and we will add 2 expression. One over the property "ConnectionString", and in Expression put

"Data Source="+yourCurrentFileVariable+";Provider=Microsoft.Jet.OLEDB.4.0;"

Your should get something like this:

"Data Source="+ @[User::current_file]+";Provider=Microsoft.Jet.OLEDB.4.0;"

Add a new expression for the property "ServerName", in this one select the current_file variable as Expression, you should get something like this in the expression field:

@[User::current_file]

7.- Now you will get an error in the OLE DB Source, don't worry it's just because the current_file variable doesn't have any value yet. Go back to the Data Flow Task in the Foreach Loop Container and set the DelayValidation attribute to true. Go to Project->"Your Package" Properties->Debugging, and set Run64BitRunteime to false.

And that's it.

Answer 2

For a one-time task, you can use throw-away VBA code.

Create a new database, and within that db create an ODBC link to your SQL Server table.

Then create a new Access query similar to this:

INSERT INTO remote_table (<field list>)
SELECT <field list>
FROM YourTable In 'C:\SourceFolder\db1.mdb';

If the fields are named the same in the source and destination, you can omit <field list>.

INSERT INTO remote_table
SELECT *
FROM YourTable In 'C:\SourceFolder\db1.mdb';

In the best case the Access field values will be compatible with the SQL Server field types. If not, you will have to use Access functions to cast the field values to SQL Server compatible types.

Once you have that sorted, a VBA procedure to upload data from your Access db files could be quick & easy:

Sub Test()
Const cstrExtension As String = "mdb"
Const cstrFolder As String = "C:\SourceFolder\"
Dim db As DAO.database
Dim strDbFile As String
Dim strInsert As String

strInsert = "INSERT INTO remote_table (<field list>)" & vbCrLf & _
    "SELECT <field list>" & vbCrLf & _
    "FROM YourTable In 'DB_FILE';"
Set db = CurrentDb
strDbFile = Dir(cstrFolder & "*." & cstrExtension)
Do While Len(strDbFile) > 0
    db.Execute Replace(strInsert, DB_FILE, _
        cstrFolder & strDbFile), dbFailOnError
    strDbFile = Dir()
Loop
Set db = Nothing
End Sub