Load data into PostgreSQL from Excel file using SSIS

Introduction

In this post, we will learn How to Load data into PostgreSQL from Excel file using SSIS PostgreSQL Destination.  We will use SSIS PowerPack to connect PostgreSQL. This article also covers how to read Excel file in SSIS.

SSIS PostgreSQL Destination can be used to perform bulk insert in PostgreSQL Table from any data source using SSIS.

In nutshell, this post will focus on how to Read Excel file and load data into PostgreSQL Table.

Prerequisite

1. First, you will need to have SSIS installed
2. Secondly, make sure to have SSDT
3. Thirdly, You have obtained PostgreSQL Credential.
4. Finally, do not forget to install ZappySys SSIS PowerPack

Components Mentioned in this article

	PostgreSQL Destination
	Excel Source

What is PostgreSQL?

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 POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

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 all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.

Getting started with using PostgreSQL has never been easier – pick a project you want to build, and let PostgreSQL safely and robustly store your data.

Getting Started

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 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.

Getting Started – Read Excel File in SSIS

Let´s start with an example. Let’s use our SSIS Excel File Source Connector, we will read data from Microsoft Excel File.

1. First of all, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
2. Now Drag and Drop ZS Excel Source from the SSIS Toolbox.
3. Let’s Double Click on Excel Source for edit and select New Connection and click on New button.
   

   Edit Excel Source

4. Now select the excel file path in and test connection in opened excel connection manager window and click on OK.
   

   Select Excel File For Connection

5. Let’s read the Excel Workbook data using table mode and select sheet to read data from that excel sheet.
   

   Load Excel Sheet Data using Access mode as a Table

6.  We can also read Excel Workbook using SQL Query mode and filter the data.
   

   Read from Excel File using SQL Query Mode

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.
   

   Read from Excel File using SQL Query Mode Examples

    

8.  That’s it we are ready to load excel file data to PostgreSQL.

Load Excel File data into PostgreSQL

Now let’s look at how to load data into target like PostgreSQL database Table.

1. First of all, let’s create the PostgreSQL Database Connection From Connection Manager.
2. Right click in connection Window and select New Connection.
   

   Connection Manager Window: Create New Connection

3. Select the ZS-POSTGRESQL Connection type from the Connection Manager Window.
   

   Select ZS-PostgreSQL Connection

4. Let’s configure the connection for PostgreSQL Database and Test the Connection.
   

   Configure PostgreSQL Connection

5. Now lets’ Drag and Drop PostgreSQL Destination from SSIS Toolbox Inside the Data Flow.
   

   Add PostgreSQL Destination

6. Connect our Source component to PostgreSQL Destination.
   

   Connect Source Component to PostgreSQL Destination

7. Double click PostgreSQL Destination to configure it. And Select the PostgreSQL Connection.
   

   Select the PostgreSQL Connection

8. Furthermore, go to Component Properties Tab and Select the PostgreSQL Table to Load Excel File data into it.
   

   Select The PostgreSQL Table

9. Finally, let’s map the columns from the Column Mappings Tab.
   

   PostgreSql Destination : Column Mappings

10. That’s it we are ready to go. Finally execute the package and it will insert the excel file data into the PostgreSQL Table.

Conclusion

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 to read the Excel file and load data into PostgreSQL using SSIS PostgreSQL Destination. You can download SSIS PowerPack here to try many other scenarios not discussed in this blog along with 70+ other components.

References

Finally, you can use the following links for more information:

• About PostgreSQL
• PostgreSQL Documentation
• Help File : PostgreSQL Destination
• Help File: Excel Source