{"id":7847,"date":"2019-08-31T08:52:43","date_gmt":"2019-08-31T08:52:43","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7847"},"modified":"2025-11-13T23:55:43","modified_gmt":"2025-11-13T23:55:43","slug":"data-migration-access-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/data-migration-access-sql-server\/","title":{"rendered":"Data migration from Access to SQL Server using SSIS Upsert Destination"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"Access<\/a>In our previous blog, we saw\u00a0how to export a REST API to MS Access using a VBA Command Button<\/a>.<\/span>\u00a0In this post, we will look at a specific example\u00a0of\u00a0data migration from Access to SQL Server using the SSIS Upsert Destination (Insert, Update, Delete)<\/strong>, along with a few other topics, such as how to create a table using the <\/span>Upsert Destination.<\/span> How to read all the customer data from the MS Access Table and merge it into the SQL Server.<\/p>\n

We will go through the steps to read data from Access and load it into SQL Server.<\/p>\n

In a nutshell, this post explains how to read data from an Access table in SSIS.<\/p>\n

So let\u2019s get started.<\/p>\n

Requirements<\/span><\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Thirdly, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n
  4. Finally, make sure that Microsoft Access is installed.<\/li>\n<\/ol>\n

    How to read MS Access table data and migrate that data to a SQL Server table.<\/h2>\n

    Let\u00b4s start with an example. In this article, we will see Data migration from Access to SQL Server.<\/p>\n

      \n
    1. First of all, drag and drop the Data Flow Task from the SSIS Toolbox and double-click it to edit.\n
      <\/div>\n
    2. Furthermore, drag and drop the OLE DB Source.\n
    3. Double-click the OLE DB Source to configure it. Click New Connection, configure the connection as shown below to connect to the Access database, and click OK.\n
    4. Now, in the OLE DB Source, set the mode to Table or View, and select Preview to view the table data.\n
    5. Now drag and drop Upsert Destination (Insert, Update, Delete), and create a connection to a SQL Server Database.\n
    6. Click the New Table button in the Target Table, change the table name, and click OK to create the table in SQL Server.\n
    7. Now select Action as Sync and check all the checkboxes: Insert, Update, and Delete from the target if not found in the source. Map all the columns and select the Key field(s) and click on OK.\n
    8. That’s it, we are ready to migrate the MS Access table data into the SQL Server Table. Execute the package, and it will migrate the data.\n
      <\/div>\n
      \"\"<\/a>

      Upsert Destination (Insert, Update, Delete)<\/p><\/div><\/li>\n<\/ol>\n

      Conclusion<\/span><\/h2>\n

      In this article, we show how to read data from an MS Access table and migrate it to a SQL Server table using SSIS. We show how to connect to MS Access using an OLE DB Source. Also, we show how to write Sync Insert, Update, and Delete in the target if not found in the Source Using\u00a0ZS Upsert Destination<\/a>.<\/span>\u00a0If you liked this\u00a0article and\u00a0want to try it, you can download the\u00a0SSIS PowerPack <\/a><\/span>here (includes 70+ Components)<\/a>.<\/p>\n

      References<\/span><\/h2>\n