Help Manual - PostgreSQL Source

Documentation on SSIS PostgreSQL Source component

Using the PostgreSQL Source Component

The PostgreSQL Source component is an SSIS data flow pipeline component that can be used to read/retrieve data from PostgreSQL instance databases. The component includes the following three pages to configure how you want to read data.

  • General
  • Columns
  • Pre & Post Command

General Page

The General page of the PostgreSQL Source component allows you to specify the general settings of the component.

PostgreSQL Source - General Page

Connection Manager

The PostgreSQL Source component requires a connection to connect to the database instance. The Connection Manager drop-down will show a list of all PostgreSQL connection managers that are available in your current SSIS package.

Data Source

This field gives you a drop-down of all the available data sources based on the connection manager chosen.

Command Timeout

The Command Timeout option allows you to specify the number of seconds for the command timeout values. The default value is 120 seconds.

Transaction Type

This option allows you to specify the type of transactions by choosing Explicit and selecting an Isolation Level or by choosing Implicit.

Transaction Isolation Level (When Transaction Type is chosen as Explicit)

The Transaction Isolation Level option allows you to specify concurrency behaviors. The below options are available.

  • Chaos
  • Read Committed
  • Read Uncommitted
  • Repeatable Read
  • Serializable
  • Snapshot
Prepare Command

This option is enabled by default. Preparing commands protects against SQL injection. Only disable this option when it is required, and you have absolute control over the values being merged into the command

Command

The Command textbox is the command text that will be executed over the Connection Manager to read data from the Data Source. A basic select statement can easily be generated by selecting a table from the Data Source property. You can then further customize the command to your liking to perform powerful queries.

Import

Loads command from a file into the Command property.

Export

Save the command in the Command property to a file.

Preview

By clicking on the Preview button, you can view up to the top 200 rows of the result set for the command you have specified.

PostgreSQL Source - Command Preview

Expression fx Button

Launches the SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Button

Generates a Word document that describes the component's metadata including relevant mapping, and so on.

Columns Page

The Columns page of the PostgreSQL Source Component shows you the available columns based on the settings on the General page.

PostgreSQL Source Component - Columns Page

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all the available fields. This is a productive way to check or uncheck all available fields.

PostgreSQL Field

This column lists the names of the fields available from the data source (or command).

Data Type

This column shows the datatype of the respective field.

Pre & Post Commands Page

The Pre & Post Commands page can be used to specify any command that needs to be run before and after the component has been executed.

PostgreSQL Source Component - Pre and Post Commands

Pre Command

The Pre Command will be executed in the pre-execute phase. Leave blank to NOT execute any command.

Success Post Command (Since v23.1)

The Success Post Command will be executed in the post-execute phase when it succeeds. Leave it blank if you do not want to execute any command.

Error Post Command (Since v23.1)

The Error Post Command will be executed in the post-execute phase when an error takes place. Leave blank to NOT execute any command.