SSIS Amazon Redshift Source
 PreviousNext

SSIS Amazon Redshift Source can be used to read data from Microsoft Amazon Redshift Database. You can use simple Table mode or write custom Query to extract desired data.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to read data from Amazon Redshift using Amazon Redshift Source(In this case its Query and Table mode).
  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 Amazon Redshift Source in the design surface.
    SSIS Amazon Redshift Source - Drag and Drop
  6. Now, we need Amazon Redshift Connection. Click here for create Amazon Redshift Connection.

How to read data from Amazon Redshift table using Amazon Redshift Source(Query Mode).

  1. Here, we are getting data using Query Mode with Dynamic expression value.
  2. Lets, create a Variable with correct DataType and Value, use following image.
    SSIS Amazon Redshift Source - Create Variables
  3. Double click on Amazon Redshift Source to configure it.
  4. Select connection we have created before, set AccessMode to Query. Click on View Examples and you can use following SQL Query(You can replace table or columns name) and insert variables. 
    SELECT  * FROM "public"."tbl_pg_Orders"
WHERE "CustomerID" like '%R%' AND "OrderAmount" > '{{User::OrderAmount}}' AND
"{{User::ColumnName}}" = '{{User::Value}}'
    SSIS Amazon Redshift Source - Read data in SQL Query mode
  5. Click on Preview button to see Data Preview.
  6. Click on OK button to save 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 Amazon Redshift 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. Thats all, just run or execute your package and see data.
    SSIS Amazon Redshift Source - Query Mode Execute

How to read data from Amazon Redshift table using Amazon Redshift Source(Table Mode).

  1. Double click on Amazon Redshift Source to configure it.
  2. Select connection we have created before, Set AccessMode to Table and Select Table from tables dropdown list.
    SSIS Amazon Redshift Source - Read data in Table mode
  3. Click on Preview button to see Data Preview.
  4. Click on OK button to save configure setting UI.
  5. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  6. Now single click on the Amazon Redshift Source, once you see blue arrow from source ... connect it to Trash Destination.
  7. Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  8. Click on OK button to save Trash Destination configure setting UI.
  9. Thats all, just run or execute your package and see data.
    SSIS Amazon Redshift Source - Query Mode Execute

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 (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
TableName Table name. This option only used when AccessMode=Table
AccessMode AccessMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Table [0] Table
Query [1] Query
Query Query to read data. This option only used when AccessMode=Query
MaxRows Maximum number of rows to fetch from source. 0=Unlimited

Setting UI

SSIS Amazon Redshift Source - Setting UI
SSIS Amazon Redshift Source - Setting UI
SSIS Amazon Redshift Source - Setting UI
SSIS Amazon Redshift Source - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Amazon Redshift Source] category
How to Read / Write Amazon Redshift Data in SSIS

How to Read / Write Amazon Redshift Data in SSIS

Introduction ZappySys provide high-performance drag and drop connectors for Amazon Redshift Integration. In our previous post we saw how to bulk load SQL data into Redshift using S3 staging technique (COPY command). Now in this post, you will see How to Read / Write Amazon Redshift Data in SSIS Data flow without need for S3 Staging. […]

Copyrights reserved. ZappySys LLC.