How to import multiple access databases into SQL server

Question

I have a folder called "Access" which contains 400 various access files. Each of these have the same structure, and 6 tables. I need to import these into Microsoft SQL Server (2014). Due to a lack of detail in the Access databases, I also need to run a normalization script after each import to make sure the relationships in the tables are kept.

I've used the wizard, and it works fine, but if there's a way to automatically import all the files and save me a week of work manually importing it would be much appreciated.

Jason B. · Accepted Answer · 2016-07-13 14:18:20Z

1

I've created SSIS packages to do this type of processing. Create a Foreach Loop Container, inside the container create a Data Flow Task that imports the files from the folder specified into your database/tables. Then create a "file system task" that moves the file to a completed folder. This could then be scheduled via sql jobs to run until all the files are processed/imported.

answered Jul 13, 2016 at 14:18

Jason B.

3251 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kedingt Over a year ago

This is working great! Thanks for the help. Turns out there are lots of exceptions in the access tables so I need to figure those out and then figure out how to run the script after each import

neer · Accepted Answer · 2016-07-13 14:02:26Z

0

Google returned this:

    INSERT INTO [dbo].[#tblImport]
        Field1,Field2,Field3,Field4
    SELECT 
        [Field1],[Field2],[Field3],[Field4] 
    FROM OPENDATASOURCE_
    ('Microsoft.Jet.OLEDB.4.0','C:\My Database.mdb')...[TableName]

Source

edited Jul 13, 2016 at 14:02

neer

4,0926 gold badges22 silver badges34 bronze badges

answered Jul 13, 2016 at 13:57

Sergio

5033 silver badges11 bronze badges

2 Comments

kedingt Over a year ago

Wouldn't this be for only one file at a time, for one table at a time? The wizard can import all tables from a file at once so it is quicker than this

Sergio Over a year ago

Sorry @kedingt, I have misread the question and thought you were trying to import access tables. You might find some T-SQL scripts out there to import entire databases too.

Collectives™ on Stack Overflow

How to import multiple access databases into SQL server

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related