Using the PostgreSQL Command Task

The PostgreSQL Command Task is a control flow component that can be used to run commands to PostgreSQL instance directly at control flow level. It can be used to execute queries, and also to get the output in a required manner.

There are three pages that can be configured.

  • General
  • Output
  • Error Handling

General

The General page includes the general properties for the component.

Connection Manager

The Connection Manager drop-down displays a list of all connection managers that are available for your current SSIS package.

Command Timeout

The Command Timeout specifies the seconds after which the command being executed would timeout.

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 (Available only when "Explicit" Transaction Type is selected)

The Transaction Isolation Level option allows you to specify concurrency behaviors for PostgreSQL tables. The below Isolation levels are available.

  • Default
  • Chaos
  • Read Committed
  • Read Uncommitted
  • Repeatable Read
  • Serializable
  • Snapshot

Prepare Command

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 to perform an action against the connected PostgreSQL instance. You can easily create a command by dragging and dropping the table names and adding the key words from the panel on the left side to the command editor window. 

Command textbox supports the use of User and System Variables. Simply select a variable under the SSIS Variables menu in the left hand side panel, and a placeholder value will be inserted into the filter text.

Import

Loads command from a file into the Command property.

Export

Save the command in the Command property to a file.

Preview

Opens a preview dialog that shows the result (up to the first 200 rows) of executing the text in the Command property over the specified Connection Manager.

Note: If the command makes any changes to the database, the changes will appear in the preview but are rolled back immediately. Changes to the database will only commit at runtime. 

Expression fx Button

Click the fx button to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Outputs

The Output   page of the PostgreSQL Command Task shows you the available output settings.

Output Type

The Output Type can be any from the below

  • None
  • Single Row
  • Scalar
  • Result Set

Single Row Selection Mode (Only for Single Row Output Type)

You could choose the Single Row Selection mode from the below.

  • First
  • Last
  • Specify Row Index
Single Row Index (Only for Single Row  Output Type)

Specify the Single Row index value in this field.

Single Row Output Variables  (Only for Single Row  Output Type)

The grid can be used to set the Column Index and the Output Variables

  • Column Index: The index for the column to be read.
  • Output variable: The variable to which it should be written
Scalar Output Variable (Only for Scalar Output Type)

Choose the variable to store the Scalar output.

Result Set Variable (Only for Result Set Output Type)

Choose the variable to store the result set output. Usually this is an Object variable.

Error Handling

The Error Handling page can be used to handle errors.

Fail on error

When chosen, will fail the component on error.

Write error to variable

Will write the error message to a variable.

Ignore error

Ignores the error and continues the task.