How to import multiple Access databases into SQL Server using SSIS

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

I searched and searched for a way to do this and I thought I was using 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.

+3


source to share


2 answers


For a one-off task, you can use drop VBA code.

Create a new database and inside this db create an ODBC link for your SQL Server table.

Then create a new Access query like 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 source and destination, you can omit <field list>

.



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

      

At best, the access field values ​​will be compatible with the SQL Server field types. Otherwise, you will have to use accessor functions to distinguish field values ​​from SQL Server compatible types.

Once you've sorted, the VBA routine to load data from your access db files can be quick and 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

      

+1


source


What you need to do is:

1.- Create 2 variables inside the package, one called "current_file" and the other called "loading_location" (or names you prefer), both with scope above the package and datatype of type, for the current file store the value empty, on load_location enter the route for your Access databases folder.

2.- Add a Foreach loop container with a collection inside, select "Expressions" and add a new expression. In the Properties field, select Directory, and in Expression, select the load_location variable you just created. Go back to the collection again and in Files put this: * .mdb

Keep all other parameters the same. Then go to Display Variables and in the variable select the current_file variable.

3.- Create a new OLE DB connection. In the "Provider Provider" field, select "OLE DB Provider for Microsoft Jet 4.0", in the database file name, select any of your access databases (this will change later, don't worry).

4.- Create a data flow task inside Foreach loop container, add OLE DB source inside this data flow task. Open OLE DB Source, in the connection manager select the connection you just created, in data access mode select "Table or View" and select the table for the database.

5.- Add the OLE DB destination, select the appropriate connection for the target database and select the table where you will put the data for the access databases.

We will now make changes for this to go through each Access database.

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

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



You may have additional material in accordance with the DB permission. Now go to the Expression attribute, expand it and we will add 2 expressions. One above the "ConnectionString" property, and in Expression it puts

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

You should end up with something like this:

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

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

@ [User :: current_file]

image

7.- Now you get an error in the OLE DB Source, don't worry about it just because the variable current_file has no value yet. Go back to the data flow task in the Foreach loop container and set the DelayValidation attribute to true. Go to Project β†’ My Package Properties-> Debugging and set Run64BitRunteime to false.

image

What is it.

+1


source







All Articles