Simple step by step process to import MS Access data into SQL Server using SSIS

By:   |   Comments (14)   |   Related: More > Import and Export


Sometimes we need to import information from MS Access. We could use the Microsoft SQL Server Migration Assistant, but sometimes we need to add custom transformations and it is necessary to use more sophisticated tools. In this tip, we are going to walk through step by step how to migrate a MS Access table to SQL Server using SQL Server Integration Services (SSIS). The tip assumes that the readers have none to little experience with MS Access and SQL Server Integration Services (SSIS).



This example is using SQL Server 2008 R2, but it should work with SQL 2005 and SQL 2008 as well. You will need to have SSIS and MS Access installed.

Getting Started

In this demonstration we are going to create a Table named Customer in MS Access and then import the table to SQL Server using SSIS.

  1. Let's start the MS Access. Go to the Windows Start Menu > All Programs > Microsoft Office and click Microsoft Access 2010

    Iniciar MS Access
  2. In Microsoft Access 2010, click the Browse icon.
    Browse the file
  3. In the File New Database, specify the file name and the path. In this instance the file name will be customer.accdb and it will be stored in the c:\ drive and press OK.
    New Database
  4. In Access, press the Create button.
  5. In the second column double click in the header and type FirstName.
  6. In second row enter a First Name.
    Create a table
  7. Add some more data to the table.
    Add data
  8. Press the Save icon and in the Save As Window, type Customer and press OK.
    Save as the table
  9. You have just created an Access Database named customer.accdb with a table named customer. Close MS Access.
  10. Let's start the SQL Server Business Intelligence Development Studio to import the table into SQL Server. Go to the Windows Start > All Programs > Microsoft SQL Server 2008 R2> SQL Server Business Intelligence Development Studio.
    Open the Development Studio
  11. In the Business Intelligence Development Studio, select File > New > Project
    Open the new project
  12. In the New Project Window, select Integration Services Project and specify a name and location for the project.
    Create a SSIS project
  13. From the Toolbox, drag and drop a DataFlow Task to the Design pane.
  14. In the design pane double click the DataFlow Task.
    Drag and drop the Datataflow
  15. In the Data Flow tab, drag and drop a OLEBD Source and Destination to the design pane.
    Data Flow tasks
  16. Click on the green arrow and drag it to join the OLEDB Source and Destination.
    Join the Data flow tasks
  17. In the OLE DB Source Editor window, press the New... button to create a new connection.
    OLE DB Source Editor
  18. In the Configure OLE DB Connection Manager, press New... again.
    Configure OLE DB Connection
  19. In the Provider combobox, select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider.
  20. In the Server or file name type the path of the Access database. In this example it is in the c:\Customers.accdb (verify the step 3 to review).
  21. Check the Blank Password option and press OK.
    Add the connection manager data
  22. In the Configure OLE DB Connection Manager, press OK
  23. In the OLE DB Source Editor, in the Name of the Table or view select the table created in Access (created in step 8).
    Add the OLEDB Source Editor data
  24. Double click the OLEDB Destination.
    Double click the OLE DB Destination
  25. In the OLE DB Destination Editor Window, in the OLE DB connection manager, press the New... button.
    OLE DB Destinatio properties
  26. In the Configure OLE DB Connection Manager, press New...
    Configure OLE DB Destination Connection
  27. In the Connection Manager make sure that the Provider combobox is using the Native OLE DB\SQL Server Native Client 10.0
  28. In the Server name, type the SQL Server Name.
  29. In the select or enter Database Name, select the SQL Server database where you want to store the Access data and press OK. You can choose any database. In this instance, a database named test is used.
    Destination Connection Manager Properties
  30. In the OLE DB Destination Editor Window, go to the Name of the table or View combobox and press the New... button.
    OLE DB Destination connection Editor
  31. In the Create Table, type this and press OK:
    CREATE TABLE [Customers](
    [ID] int,
    [FirstName] nvarchar(255)

    Create destination table
  32. In the OLE DB Destination Editor Click on the Mappings page and press OK.
    Map the columns
  33. Now the project is ready to start importing the data from MS Access to SQL Server. Now, press the start debugging icon to start importing the data.
    Run the start debugging
  34. To verify that everything worked, open SQL Server Management Studio and verify the data is in the database used for the import. In this case, the test database was used (verify this from step 29).
  35. Finally query the table to verify the data was successfully imported.
    Verify the imported data
  36. Congratulations! You have created an SSIS project to import data from MS Access to SQL Server.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 27, 2018 - 11:22:47 PM - Yash Back To Top (75539)


 Hello Experts,


I have Multiple Ms acess files with same name and want to transfer the data in SQL Server using SSIS. I got the concept of using Foreach loop when we can make use of text files . Will it be the same process to do for the Ms Access (Mdb) files too.




Monday, June 15, 2015 - 3:48:42 PM - Daniel Back To Top (37928)

You can use a Lookup:

Monday, June 15, 2015 - 2:54:16 PM - Yariv Back To Top (37926)

Hi Daniel,

Let assume that I have a table names Source in the Access DB and a table named Destination on the SQL server 2008.

Is it possibole to run a daily job that will imoprt only the records that exists in the source table but not  in the destination tabls?



Thursday, February 26, 2015 - 10:28:02 AM - Daniel Back To Top (36359)

In the step 21, add the password.

Thursday, February 26, 2015 - 2:17:26 AM - Jonathan Back To Top (36353)

Hi!, Do you have any example for an access file that have passwords? It seems that my file doesnt feed to the database because of this. any suggestions? TIA

Friday, January 3, 2014 - 2:13:17 PM - John G Back To Top (27952)

You would not belive the gyrations I went through to import an Access 2013 database into SQL Server before I saw Todd's link to get the OLE DB provider... Thanks for a great tip and thanks to Todd for very helpful feedback!

Friday, May 31, 2013 - 11:21:36 AM - anonymous Back To Top (25215)

Extremely helpful...Thanks to Daniel and Kevin too!!

Saturday, May 11, 2013 - 8:04:58 AM - hamed Back To Top (23899)

hi Daniel, it was good but i have problem! my access file is linked to share point, so i can't use this file!

can you help me?



Thursday, January 31, 2013 - 2:23:25 PM - Anita Back To Top (21852)


All I want to do is create a totals query.  But, I keep getting a message saying I can't do to the * mark in a table field that has no information in it.  How do I get rid of the asterisk so I can get a totals query to work?

Tuesday, October 30, 2012 - 10:22:52 AM - Todd Back To Top (20147)


I found it - you can download the OLE DB provider which is included in the MS Access 2010 Engine here -



Tuesday, October 30, 2012 - 10:17:34 AM - Todd Back To Top (20146)

Hi Daniel,

 I am walking through the steps, but am missing the provider in the Connection Manager - Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I am using SQL Server 2008 R2 as you stated.  Am I missing something from my setup or can I load this provider independently?



Tuesday, June 12, 2012 - 7:31:48 AM - Adi Back To Top (17942)

There's another way to do it, avoiding all steps appear below, also avoiding the 32/64bit issues -

It's called ClipTable, and it's available for free at



Tuesday, May 8, 2012 - 11:40:56 AM - Daniel Calbimonte Back To Top (17341)

Thanks a lot Kevin !

Tuesday, May 8, 2012 - 8:53:56 AM - Kevin Archibald Back To Top (17338)

If you follow these instructions on a 64 bit machine, you will become frustrated as it will fail in debugging!

If you are working with ACCESS databases in SSIS's on a 64 bit machine, you have to right click on the dtsproj and set CONFIGURATION PROPERTIES / DEBUG OPTIONS / 64 BIT RUNTIME to FALSE


Also if you are using DTSEXEC to run the finished product, make sure you are using the 32 bit version.

get free sql tips
agree to terms