Help Manual - Amazon RedShift Destination

Documentation on SSIS Amazon RedShift Destination component

Using the Amazon RedShift Destination Component

The Amazon Redshift Destination component can be used to write to a Destination table. The component includes four pages:

  • General
  • Columns
  • Pre & Post Commands
  • Error Handling

General Page

The General page of the Amazon Redshift Destination component allows you to specify the general settings of the component.

Amazon RedShift Destination - General

Connection Manager

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

Action

The action or command you want to execute on the Destination Table. Available actions include:

  • Insert: Add records to the Destination Table.
  • Update: Update existing records in the Destination Table.
  • Upsert: If the specified record exists in the Destination Table it is updated otherwise, it is inserted.
  • Delete: Delete existing records from the Destination Table.
  • Custom Command: A custom command like a procedure can be specified in the provided space.
Destination

The Destination Table drop-down displays a list of available tables for the instance specified in the Connection Manager.

Create Table

Opens the below Amazon Redshift Table creator.

Amazon Redshift Destination - Create Table

It auto-generates a command based on the selected Connection Manager and Input Columns to create a new table. You can further customize the command to suit your needs and then click the 'Execute Command' button. The table you created will be selected in the Destination Table property.

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
Write Mode

There are two write modes from which you can select.

  • Row by Row
  • Batch (for improved performance)
Batch Size

The Batch Size option, which is available when you choose Batch in Write Mode, allows you to specify how many records you want to send to the target database server at a time. The default value is 100.

Bulk Upload Connection

The Bulk Upload Connection option is available when the Write Mode is chosen as “Bulk”. The connection can be chosen to specify which storage to use for stage files in an external location. Amazon S3 Connection is supported.

Bulk Upload Path

The Bucket Upload location from Amazon S3 can be selected by clicking on the ellipsis (…) button.

Remove Uploaded Files Upon Completion

This option can be enabled to remove the files from the stage location once the copy is done. Note that the file would be deleted regardless of whether the write operation was successful or not.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document that describes the component's metadata including relevant mapping, and so on.

Columns Page

The Columns page of the Amazon Redshift Destination Component allows you to map the columns from upstream components to fields of the specified Destination Table in the General Page. The Columns Page is not available when the selected action is Custom Command.

Amazon RedShift Destination - Columns Page

  • Input Column: Select an Input Column from an upstream component here.
  • Destination Table Column: This is the field you are writing data to.
  • Lookup: Enable the lookup feature by mapping the virtual '<Lookup>' input column to the destination table column. When this option is selected, the component can perform a lookup based on input values. For further information about how to use the Lookup feature, please refer to the Working with the Lookup Feature section below.
  • Data Type: This column indicates the type of value for the current field.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Working with the Lookup Feature

The Lookup feature allows you to perform a lookup based on the input values. To configure the Lookup feature, you map the destination table column with the virtual '<Lookup>' input column. You will be presented with the following screen.

Amazon Redshift Destination - Lookup

Target Table

The Target Table is a read-only property that shows the destination table configured in the Amazon Redshift Destination component.

Target Column

The Target Column is a read-only property that shows the target column which is configured to use the Lookup feature.

Lookup Table

Select the Lookup Table from the drop-down list that displays available tables for the database specified in the Connection Manager.

Returning Column

Select the Returning Column from the drop-down list that displays available columns for the specified Lookup Table.

Default Value

When the Default Value is specified, the component will use this default value to write to the target column should the input value lookup fail

Lookup Condition
  • + sign: Add lookup condition.
  • - sign: Remove lookup condition.
  • Arrows: Use arrows to group the lookup conditions.
  • AND/OR: Specify AND or OR to create logical expressions of your lookup conditions.
  • Lookup Column: Select the lookup column from the drop-down list which displays available columns for the specified Lookup Table.
  • Operator: Use the query operator to specify how each input value in a clause must relate to the corresponding value in a lookup table.
    • =: the value of the field is equal to the selected lookup value
    • <>: the value of the field is not equal to the selected lookup value
    • >: the value of the field is greater than the selected lookup value
    • <: the value of the field is less than the selected lookup value
    • >=: the value of the field is greater than or equal to the selected lookup value
    • <=: the value of the field is less than or equal to the selected lookup value
    • LIKE: the value of the field is equal to the selected lookup value based on pattern match, supports %, _, [ ], and [^] wildcards
      • Note: This option may impact performance, consider using = whenever possible.
    • NOT LIKE: the value of the field is not equal to the selected lookup value based on pattern match, supports %, _, [ ], and [^] wildcards
      • Note: This option may impact performance, consider using <> whenever possible.
    • BETWEEN: the value of the field is between the values of two selected lookup values
    • IS NULL: the value of the field is NULL
    • IS NOT NULL: the value of the field is not NULL
  • Input Value: The Input Value for the lookup condition. Available options are:
    • Input Column
      • Enables user to perform Lookup match by selecting a field from the Lookup Table Column
    • Variable
      • Enables user to perform Lookup match based on a System or User SSIS variable
    • Static Value
      • Enables user to perform Lookup match based on a static value

Pre & Post Commands Page

The Pre & Post Commands page allows you to specify commands you wish to execute before and after component execution

Amazon Redshift Destination - Pre & Post Commands Page

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.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

Amazon Redshift Destination - Error Handling Page

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the server or the component itself.

Amazon Redshift Destination - Error Output

In the Error Handling page, there are also options that can be used to enable or disable the following output fields for the destination component's Default Output.

_AffectedRows: Reports the number of affected rows for the SQL script executed for each incoming row. This option is not available when the Batch mode is used.