{"id":7903,"date":"2019-09-06T09:02:51","date_gmt":"2019-09-06T09:02:51","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7903"},"modified":"2019-10-14T20:57:35","modified_gmt":"2019-10-14T20:57:35","slug":"ssis-amazon-redshift-upsert-update-insert-delete","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-amazon-redshift-upsert-update-insert-delete\/","title":{"rendered":"Merge \/ Upsert data in Amazon Redshift using SSIS"},"content":{"rendered":"

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

\"Access-to-AmazonRedshift\"<\/a>In our previous blog we saw how update \/ insert data into SQL Server using SSIS Upsert Destination<\/a>. In this post we will look at specific example on Data migration from Access to Amazon Redshift using SSIS Upsert Destination (Insert, Update, Delete)<\/strong>, along with few other topics such as how to create table using Upsert Destination<\/a>. how to read all Customers data from Ms Access Table and Merge it in the Amazon Redshift.<\/p>\n

We will go through the steps to read data from Access and Load into Amazon Redshift.<\/p>\n

In nutshell,\u00a0this post will focus on how to read access table data 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 installed.<\/li>\n<\/ol>\n

    How to Read MS Access table data and migrate that data in Amazon Redshift table.<\/h2>\n

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

      \n
    1. First\u00a0of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.\n
    2. Furthermore, drag and drop the OLE DB Source.\n
    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.\n
    4. Now in OLE DB Source Select the mode as Table or View and select Preview to view the access table data.\n
    5. Now drag and drop Upsert Destination (Insert, Update, Delete) and create connection with Amazon Redshift Database.\n
    6. Now select Action as Sync and check all the checkboxes Insert, Update and Delete from target if not found in source. Select the table and Map all the columns and select the Key field(s) and click on OK.\n
    7. That’s it we are ready to migrate MS access table data into Amazon Redshift Table. Execute the package and it will migrate the data.\n
      \"Upsert<\/a>

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

      Bulk Update data in Amazon Redshift<\/h2>\n

      So in previous example we saw bulk update or insert (Upsert) in Redshift Table. Now let’s look at how to update data in target table if record exists.<\/p>\n

      Here is how you can perform bulk update in Amazon Redshift using Upsert Destination.<\/p>\n

        \n
      1. Double click on Upsert Destination for configure it.<\/li>\n
      2. Set Action Bulk Update => based on matching records on target. Select Connection and Target Table. Click on Map All to Mappings all columns and check on Only Primary Key columns.
        \n\"SSIS<\/li>\n
      3. Thats all, Click on OK to save Upsert Destination settings UI.<\/li>\n<\/ol>\n

        Bulk Delete data in Amazon Redshift<\/h2>\n

        Here is how you can bulk delete data in Amazon Redshift.<\/p>\n

          \n
        1. Double click on Upsert Destination for configure it.<\/li>\n
        2. Set Action Bulk Delete => based on matching records on target. Select Connection and Target Table. Click on Map All to Mappings all columns and check on Only Primary Key columns.
          \n\"SSIS<\/li>\n
        3. Thats all, Click on OK to save Upsert Destination settings UI.<\/li>\n<\/ol>\n

           <\/p>\n

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

          In this article, we show how to read MS Access table data and migrate the data in Amazon Redshift 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<\/a>. If you liked this\u00a0article and you want to try, you can download the SSIS PowerPack from here (includes 70+ Components)<\/a>.<\/p>\n

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