SSIS Amazon Redshift Cluster Management Task
 PreviousNext

SSIS Amazon Redshift Cluster Management Task can be used to automate most common Redshift Cluster Management Tasks such as Create Cluster, Delete Cluster, List Cluster Properties, Create Snapshot, Delete Snapshot etc.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this section you will learn how to Create and Delete Cluster and Snapshot using ZS Amazon Redshift Cluster Task with few clicks.
  1. Firstly, In order to connect to Amazon Storage for Redshift from SSIS you will need Credentials. Ask your SysAdmin or responsible person to provide that to you. Your keys will look something like this (this is just example key which may differ in your case).

    Read more for How to get your AccessKey and Secret Key click here. 
    AccessKey: AKIAIOSFODNN7EXAMPLE
SecretKey: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
  2. Once you have Access Key and Secret Key you may proceed to create new SSIS package. In BIDS/SSDT create new SSIS package Project.
  3. Here, we are showing you how to Create multiple Action, Create Redshift Cluster, Delete Redshift Cluster, Loop through using SSIS ZS Amazon Redshift Cluster Task Properties. First of all you need to create Design like below we have in image. Just Drag and Drop Components from SSIS Toolbox.
    SSIS Amazon Redshift Cluster Management Task - Create, Delete, Loop through Redshift Cluster Properties, Create, Delete Snapshot
  4. Now we need AWS-Storage connection.

How to create AWS-Storage connection.

  1. Right click on Connection Managers Panel to Create New ZS-AWS-Storage Connection for Redshift, and Context Menu will appear, Select New Connection from the Context Menu.
    SSIS Amazon Redshift Create Connection
  2. Select ZS-AWS-Storage Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS Amazon Redshift Create Connection
  3. Now in Connection Manager UI, Select Redshift in Storage Service and Enter your Amazon Redshift Access Key and Secret Key and leave all other properties as it is, and Click Test Connection.
    SSIS Amazon Redshift Create Connection
  4. If test successful then hit OK to save connection manager setting UI.

How to Create Redshift Cluster.

  1. Double click on Amazon Redshift Cluster Task to configure it.
  2. Set Action to Create Redshift Cluster, Select AWS connection, enter Cluster Identifier. In the Create Options Tab check on Skip cluster create if already exists. Set master password, check on Add Access Rule and leave other all properties as it is.
    SSIS Amazon Redshift Cluster Management Task - Create Cluster Option
  3. In the General Tab you can set Wait until Maximum Time.
    SSIS Amazon Redshift Cluster Management Task - Create Cluster Wait Options
  4. Click on OK button to save configure setting UI.

How to Delete Redshift Cluster.

  1. Double click on Amazon Redshift Cluster Task to configure it.
  2. Set Action to Delete Redshift Cluster, select AWS connection, enter Cluster Identifier. Check on Skip delete if cluster not found Skip Final Snapshot.
    SSIS Amazon Redshift Cluster Management Task - Create Cluster Option
  3. Click on OK button to save configure setting UI.

How to get attribute or cluster using Amazon Redshift Management Cluster Task.

  1. Double click on Amazon Redshift Cluster Task to configure it.
  2. Set Action to Fetch Single Attribute of Cluster, select AWS connection, enter Cluster Identifier. Check on Skip fetch if cluster not found, set Property Name to ClusterStatus and select Result Variable or Hit on New Button to store cluster status in Result Variable.
    SSIS Amazon Redshift Cluster Management Task - Create Cluster Option
  3. Click on OK button to save configure setting UI.

How to Create Snapshot using Amazon Redshift Management Cluster Task.

  1. Double click on Amazon Redshift Cluster Task to configure it.
  2. Set Action to Create Snapshot, select AWS connection, enter Cluster Identifier. Enter your Snapshot Identifier otherwise you can leave blank to assign system generated Id.
    SSIS Amazon Redshift Cluster Management Task - Create Snapshot Options
  3. Click on OK button to save configure setting UI.

How to Delete Snapshot using Amazon Redshift Management Cluster Task.

  1. Double click on Amazon Redshift Cluster Task to configure it.
  2. Set Action to Delete Snapshot, select AWS connection, enter Cluster Identifier. Enter your Snapshot Identifier.
    SSIS Amazon Redshift Cluster Management Task - Delete Snapshot Options
  3. Click on OK button to save configure setting UI.
  4. Finally, almost you are done with each Action configurations, just need to Execute Package, So for that Right Click on Package from Solution Explorer and Select Execute Package
    SSIS Amazon Redshift Cluster Management Task

Properties

Property Name Description
Select Action Select Action. It can be Create Redshift Cluster, Delete Redshift Cluster, Create Snapshot, Delete Snapshot, Fetch Single Attribute of Cluster and Fetch All Attributes of Cluster as Table. For all you need to create Amazon Redshift connection.

Available Options

Option Description
Create Redshift Cluster Create Redshift Cluster
Delete Redshift Cluster Delete Redshift Cluster
Create Snapshot Create Snapshot
Delete Snapshot Delete Snapshot
Fetch Single Attribute of Cluster Fetch Single Attribute of Cluster
Fetch All Attributes of Cluster as Table Fetch All Attributes of Cluster as Table
Cluster Identifier

A unique cluster identifier.

Create Option This is available when you select Create Redshift Cluster from Select Action.

Available Options

Option Description
Skip cluster create Skip cluster create if already exists
Cluster Type single-node
multi-node
Node Type dw2.large
ds1.xlarge
ds1.8xlarge
ds2.xlarge
ds2.8xlarge
dc1.large
dc1.8xlarge
Number of nodes You can select number of nodes
Master Username You can enter master username
Master Password You can create master password
Availibility Zone Availibility Zone
First Database Name Give your first Database Name
Vpc Security Group Name Vpc Default security group name
Allow Public Access You can check Allow Public Access if you want
Add Access Rule Access Rule Info
Option Description
Protocol tcp
udp
icmp
all
Port Range Port Range
IP Range IP Range
Vpc Security Group Ids Vpc Security Group Ids
Cluster Security Groups Cluster Security Groups
Delete Option This is available when you select Delete Redshift Cluster from Select Action.

Available Options

Option Description
Skip Delete Skip delete if cluster not found
Skip Final Snapshot Check if you want to skip final Snapshot
Snapshot Name You can write snapshot name
Create Snapshot This is available when you select Create Snapshot from Select Action.

Available Options

Option Description
Snapshot Identifier You can give or leave blank to assign system generated Id
Delete Snapshot This is available when you select Delete Snapshot from Select Action.

Available Options

Option Description
Snapshot Identifier Give snapshot name
Fetch Single Attribute of Cluster This is available when you select Fetch Single Attribute of Cluster from Select Action.

Available Options

Option Description
Skip fetch if cluster not found Check for skip if cluster not found
Property Nname Property Nname
Result Variable Variable allows to use store result in variable
Fetch All Attributes of Cluster This is available when you select Fetch Single Attribute of Cluster from Select Action.

Available Options

Option Description
Skip fetch if cluster not found Check for skip if cluster not found
Fetch all clusters Fetch all clusters
Result Variable Variable allows to use store result in variable
General

Wait until selected action is completed
Maximum wait time

Connection Connection where you want to create connection
LoggingMode

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.

Setting UI

SSIS Amazon Redshift Cluster Task
SSIS Amazon Redshift Cluster Task

See Also

References

Copyrights reserved. ZappySys LLC.