SSIS PostgreSQL Source
 PreviousNext

SSIS PostgreSQL Source can be used to extract large amount of data from a PostgreSQL Database. This component supports SQL   language to query PostgreSQL data. PostgreSQL is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance.

Download SSIS PowerPack

Video Tutorial


Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to extract data from PostgreSQL Server using ZS PostgreSQL Source.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the PostgreSQL Source in the design surface.
    SSIS PostgreSQL Source - Drag and Drop
  6. Now, we need PostgreSQL Connection.

How to create PostgreSQL Connection.

  1. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  2. Select ZS-POSTGRESQL Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS PostgreSQL Connection - ADD
  3. On New Connection Dialogbox enter connection attributes to connect to PostgreSQL instance and Click Test connection to verify credentials.
    SSIS PostgreSQL connection
  4. Click on Test Connection to check configure correct or not.
  5. Click on OK button to save PostgreSQL Connection Manager configure setting UI.

How to read data from PostgreSQL Server using PostgreSQL Source with Table Mode.

  1. Double click on PostgreSQL Source to configure it.
  2. In the PostgreSQL Source, select connection we have created, set Access Mode to Table and select table that you want to use as source.
    SSIS PostgreSQL Source - Table Mode
  3. Click on Preview button to see Data Preview.
  4. Now in the columns tab, you can select columns you want.
    SSIS PostgreSQL Source - Select Columns
  5. Now, click on OK button to save PostgreSQL Source configure setting UI.
  6. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  7. Now single click on the PostgreSQL Source, once you see blue arrow from source ... connect it to Trash Destination.
  8. Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  9. Click on OK button to save Trash Destination configure setting UI.
  10. Execute the package and verify source data in file.
    SSIS PostgreSQL Source Execute - Table Mode

How to read data from PostgreSQL Server using PostgreSQL Source with Query Mode.

  1. Here, we are getting data using SQL Query with Dynamic expression value.
  2. Lets, create a Variable with correct DataType and Value(You can write SQL Query in value too).
    SSIS PostgreSQL Source - Create Variable
  3. Double click on PostgreSQL Source to configure it.
  4. Select connection and set AccessMode to Query. Use following query to read data from PostgreSQL Table and replace table name. 
    SELECT "OrderID", "CustomerID", "OrderDate", "OrderAmount"
FROM public."tbl_pg_Orders"
WHERE "CustomerID" = '{{User::CustomerID}}';
    SSIS PostgreSQL Source - Query Mode
  5. Click on Preview button to see Data Preview.
  6. Now, click on OK button to save PostgreSQL Source configure setting UI.
  7. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  8. Now single click on the PostgreSQL Source, once you see blue arrow from source ... connect it to Trash Destination.
  9. Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  10. Click on OK button to save Trash Destination configure setting UI.
  11. Execute the package and verify source data in file.
    SSIS PostgreSQL Source Execute - Query Mode

Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options

Option Description
Normal Normal
Medium Medium
Detailed Detailed
Debugging Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
TableName Table name. This option is only used when AccessMode=Table. It is used to specify the table's name.
AccessMode AccessMode

Available Options

Option Description
Table Table
Query Query
Query SQL Query to read data. This option only used when AccessMode=Query. The option allows to create custom queries instead of a selected table.
MaxRows Maximum number of rows to fetch from source. 0=Unlimited

Error Handling

  • This component supports error output.
  • Any bad value will cause Error or Truncation (e.g. If your sample value is 5 character long but output column length is set to 3 chars). If you have error output attached with RedirectRow option on Error, Truncation then you can capture bad rows along with exact reason of error.
  • Use error handling tab to set option for error handling. By default component fails on any data error or truncation issue

SettingUI

SSIS PostgreSQL Source Execute - Setting UI
SSIS PostgreSQL Source Execute - Setting UI
SSIS PostgreSQL Source Execute - Setting UI
SSIS PostgreSQL Source Execute - Setting UI

FAQ

References

See Also

Articles / Tutorials

Click here to see all articles for [SSIS PostgreSQL Source] category
Export PostgreSQL Table Data to SQL Server in SSIS

Export PostgreSQL Table Data to SQL Server in SSIS

ZappySys provides high performance drag and drop connectors for PostgreSQL Integration. In this post you will see how to Export PostgreSQL Table Data to SQL Server Table using SSIS PostgreSQL Source. PostgreSQL Source supports SQL language to query PostgreSQL data. PostgreSQL is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance. […]

Load PostgreSQL Table Data to CSV File in SSIS

Load PostgreSQL Table Data to CSV File in SSIS

Introduction ZappySys provides high performance drag and drop connectors for PostgreSQL Integration. In this post you will see how to Load PostgreSQL Table Data to CSV File using SSIS PostgreSQL Source. PostgreSQL Source supports SQL language to query PostgreSQL data. PostgreSQL is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance. In […]

Copyrights reserved. ZappySys LLC.