{"id":6815,"date":"2019-04-22T12:25:36","date_gmt":"2019-04-22T12:25:36","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6815"},"modified":"2019-09-11T10:20:25","modified_gmt":"2019-09-11T10:20:25","slug":"ssis-load-data-postgresql-from-excel-file","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-load-data-postgresql-from-excel-file\/","title":{"rendered":"Load data into PostgreSQL from Excel file using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"PostgreSql<\/a>
\nIn this post, we will learn How to Load data into PostgreSQL from Excel file using SSIS PostgreSQL Destination<\/strong>.\u00a0 We will use\u00a0SSIS PowerPack<\/a>\u00a0to connect PostgreSQL. This article also covers how to read Excel file in SSIS.<\/p>\n

SSIS PostgreSQL Destination<\/a>\u00a0can be used to perform bulk insert in PostgreSQL Table from any data source using SSIS.<\/p>\n

In nutshell,\u00a0this post will focus on how to Read Excel file and load data into PostgreSQL Table.<\/p>\n

Prerequisite<\/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, You have obtained PostgreSQL Credential.<\/li>\n
  4. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n<\/ol>\n

    Components Mentioned in this article<\/strong>
    \n

    \n\n\n\n\n
    \"SSIS<\/td>\nPostgreSQL Destination<\/a><\/td>\n<\/tr>\n
    \"SSIS<\/td>\nExcel Source<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    What is PostgreSQL?<\/span><\/h2>\n

    PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the\u00a0POSTGRES<\/a>\u00a0project at the University of California at Berkeley and has more than 30 years of active development on the core platform.<\/p>\n

    PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on\u00a0all major operating systems<\/a>, has been\u00a0ACID<\/a>-compliant since 2001, and has powerful add-ons such as the popular\u00a0PostGIS<\/a>\u00a0geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.<\/p>\n

    Getting started<\/a>\u00a0with using PostgreSQL has never been easier \u2013 pick a project you want to build, and let PostgreSQL safely and robustly store your data.<\/p>\n

    Getting Started<\/h2>\n

    In order to start, we will show several examples of how to connect the excel file and read data from the excel worksheet. ZappySys includes an SSIS Excel File Source Connector<\/a> that will help you in reading excel sheet data from Excel File. Here we are showing you is, How to Load Excel File data in PostgreSQL using SSIS PostgreSQL Destination<\/a>.<\/p>\n

    Getting Started – Read Excel File in SSIS<\/h2>\n

    Let\u00b4s start with an example. Let’s use our\u00a0SSIS Excel File Source Connector<\/a>, we will read data from Microsoft Excel File.<\/p>\n

      \n
    1. First of all, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.\"Drag<\/a><\/li>\n
    2. Now Drag and Drop ZS\u00a0Excel Source from the SSIS Toolbox.\""<yoastmark\"<\/a><\/li>\n
    3. Let’s Double Click on Excel Source for edit and select New Connection and click on New button.\n
    4. Now select the excel file path in and test connection in opened excel connection manager window and click on OK.\n
    5. Let’s read the Excel Workbook data using table mode and select sheet to read data from that excel sheet.\n
    6. \u00a0We can also read Excel Workbook using SQL Query mode and filter the data.\n
    7. You can do various other things also in the query mode, find our various example here to read Excel Workbook using SQL Query mode.\n
    8. \u00a0That’s it we are ready to load excel file data to PostgreSQL.<\/li>\n<\/ol>\n

      Load Excel File data into PostgreSQL<\/h2>\n

      Now let’s look at how to load data into target like PostgreSQL database Table.<\/p>\n

        \n
      1. First of all, let’s create the PostgreSQL Database Connection From Connection Manager.<\/li>\n
      2. Right click in connection Window and select New Connection.\n
      3. Select the ZS-POSTGRESQL Connection type from the Connection Manager Window.\n
      4. Let\u2019s configure the connection for PostgreSQL Database and Test the Connection.\n
      5. Now lets’ Drag and Drop PostgreSQL Destination from SSIS Toolbox Inside the Data Flow.\n
      6. Connect our Source component to PostgreSQL Destination.\n
      7. Double click PostgreSQL Destination to configure it.\u00a0And Select the PostgreSQL Connection.\n
      8. Furthermore, go to Component Properties Tab and Select the PostgreSQL Table to\u00a0Load Excel File data into it.\n
      9. Finally, let’s map the columns from the Column Mappings Tab.\n
      10. That’s it we are ready to go. Finally execute the package and it will insert the excel file data into the\u00a0PostgreSQL Table.<\/li>\n<\/ol>\n

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

        Above all, in this blog, we learned how to Load data into PostgreSQL from Excel file using SSIS PostgreSQL Destination using SSIS Pack. Therefore we used Excel File Source Connector<\/a>\u00a0to read the Excel file and load data into PostgreSQL using SSIS PostgreSQL Destination<\/a>. You can download SSIS PowerPack here<\/a> to try many other scenarios not discussed in this blog along with 70+ other components.<\/p>\n

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

        Finally, you can use the following links for more information:<\/p>\n