Data migration from Access to SQL Server using SSIS Upsert Destination

Introduction

In our previous blog we saw How to Export REST API to MS Access using VBA Command Button. In this post we will look at specific example on Data migration from Access to SQL Server using SSIS Upsert Destination (Insert, Update, Delete), along with few other topics such as how to create table using Upsert Destination. how to read all Customers data from Ms Access Table and Merge it in the SQL Server.

We will go through the steps to read data from Access and Load into SQL Server.

In nutshell, this post will focus on how to read access table data in SSIS.

So let’s get started.

Requirements

1. First, you will need to have SSIS installed
2. Secondly, make sure to have SSDT
3. Thirdly, do not forget to install ZappySys SSIS PowerPack
4. Finally, Make sure that Microsoft Access installed.

How to Read MS Access table data and migrate that data in SQL Server table.

Let´s start with an example. In this article we will see Data migration from Access to SQL Server.

1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
   

   Drag and Drop : SSIS Data Flow Task from SSIS Toolbox

2. Furthermore, drag and drop the OLE DB Source.
   

   OLE DB Source – Drag and Drop

3. Double click on OLE DB Source for configure it and click on New Connection and configure connection as below to connect access database and click on OK.
   

   OLE DB : Access Connection

4. Now in OLE DB Source Select the mode as Table or View and select Preview to view the access table data.
   

   OLE DB Source Preview

5. Now drag and drop Upsert Destination (Insert, Update, Delete) and create connection with SQL Server Database.
   

   Upsert Destination : SQL Server Connection

6. Click on New Table button of Target Table and change the table name and click on OK to create table in SQL Server.
   

   Upsert Destination : Create Table

7. Now select Action as Sync and check all the checkboxes Insert, Update and Delete from target if not found in source. Map all the columns and select the Key field(s) and click on OK.
   

   Upsert Destination Configuration

8. That’s it we are ready to migrate MS access table data into SQL Server Table. Execute the package and it will migrate the data.
   

   Upsert Destination (Insert, Update, Delete)

Conclusion

In this article, we show how to read MS Access table data and migrate the data in SQL Server table using SSIS. We show how to do connect access MS Access using OLE DB Source. Also, we show how to write Sync Insert, Update and Delete in target if not found in Source Using ZS Upsert Destination. If you liked this article and you want to try, you can download the SSIS PowerPack from here (includes 70+ Components).

References

• ZappySys SSIS installer.
• Help File: Upsert Destination