Introduction
ZappySys provide high-performance drag and drop connectors for Amazon Redshift Integration. In previous post we saw how to read / write Amazon Redshift data in SSIS. Now in this post, you will see how to Execute SQL query against Amazon Redshift in SSIS using ZS Amazon Redshift ExecuteSQL Task. SSIS Redshift Execute SQL Task can be used to send any SQL queries to Redshift Cluster without needing ODBC driver. You can Issue various SQL commands such as DROP, COPY, UNLOAD, CREATE, etc very easily. Load query at runtime from variable, file or Direct value. You can also issue SQL query to fetch resultset to store in variable and then loop records using this technique.
Prerequisite
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- Thirdly, You have obtained Amazon Redshift Credential.
- Finally, do not forget to install ZappySys SSIS PowerPack.
Component Mention in this article
What is Amazon Redshift
Before we talk Execute SQL query against Amazon Redshift in SSIS lets talk what is Amazon Redshift (or sometimes referred to as AWS Redshift). Amazon Redshift is a Cloud-based Data warehouse service. This type of system also referred to as MPP (Massively Parallel Processing). Amazon Redshift uses a highly modified version of PostGrey SQL Engine behind the scene. Amazon Redshift provides the advantage of Scale as you go, at a very low cost compared to onsite dedicated hardware/software approach. In this article, we will try to learn how to Execute SQL query against Amazon Redshift in SSIS.
Getting Started
In order to start, we will show several examples. ZappySys includes an Amazon Redshift ExecuteSQL Task, that will help you how to Create and Drop table by Execute SQL query against Amazon Redshift in SSIS. You can connect to your Amazon Redshift Account by entering credentials.
Execute SQL query against Amazon Redshift in SSIS
Let’s start with an example, In this tutorial, we will learn how to CREATE and DROP table using SQL query against Amazon Redshift ExecuteSQL server using ZS Amazon Redshift ExecuteSQL Task.
- In order to connect to the Amazon Redshift Database from SSIS, you will need credentials. Ask your SysAdmin or responsible person to provide that you.
- Once you finished the first step, Open Visual Studio and Create New SSIS Package Project.
- In your SSIS toolbox, you should see many new Icons starting with “ZS” (i.e. ZappySys). If you don’t see them then check this.
- Here, In Visual Studio, drag and drop the Amazon Redshift ExecuteSQL Task in the design surface.
SSIS ZS Amazon Redshift ExecuteSQL Task – Drag and Drop
- Now we need Amazon Redshift Connection. Click here to Create Amazon Redshift Connection.
- Double click on Amazon Redshift ExecuteSQL Task to configure it.
- Select connection we have created before, set Sql Source Type to DirectValue, Result Type to FullResultSet and select object Datatype Variable and Use following SQL Query for Amazon Redshift.
12345678DROP TABLE IF EXISTS CustomerData;CREATE TABLE CustomerData(Id bigint NULL,CustomerID VARCHAR(5) NOT NULL,CustomerName VARCHAR(50) NOT NULL,ContactName VARCHAR(40) NOT NULL,CustomerCity VARCHAR(50) NOT NULL)
SSIS Amazon Redshift ExecuteSQL Task – Configure
- Click on OK button to save configure setting UI.
- That’s all, right-click on the task and run or Execute. This should create a new table in Redshift.
Run or Execute SSIS ZS ExecuteSQL Task Package
Conclusion
After all, In this blog, we learned how to Execute SQL query against Amazon Redshift in SSIS using ZS Amazon Redshift ExecuteSQL Task. You can download SSIS PowerPack here to try many other scenarios not discussed in this blog along with 70+ other components.
References
Finally, you can use the following links for more information:
