{"id":7760,"date":"2019-08-14T12:43:11","date_gmt":"2019-08-14T12:43:11","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7760"},"modified":"2019-09-04T15:30:41","modified_gmt":"2019-09-04T15:30:41","slug":"execute-sql-query-amazon-redshift-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/execute-sql-query-amazon-redshift-ssis\/","title":{"rendered":"Execute SQL query against Amazon Redshift in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>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<\/a>. Now in this post, you will see how to Execute SQL query against Amazon Redshift in SSIS using ZS Amazon Redshift ExecuteSQL Task.\u00a0SSIS Redshift Execute SQL Task<\/em>\u00a0can 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\u00a0query 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<\/a>.<\/p>\n

 <\/p>\n

Prerequisite<\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Thirdly, You have obtained Amazon Redshift Credential.<\/li>\n
  4. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack.<\/a><\/li>\n<\/ol>\n

    Component Mention in this article<\/h2>\n
    \n\n\n\n
    \"\"<\/a><\/td>\nAmazon Redshift ExecuteSQL Task<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    What is Amazon Redshift<\/h2>\n

    Before we talk Execute SQL query against Amazon Redshift in SSIS\u00a0lets talk what is\u00a0Amazon Redshift<\/em>\u00a0(or sometimes referred to as\u00a0AWS Redshift<\/em>).\u00a0Amazon Redshift<\/b>\u00a0is a Cloud-based Data warehouse service. This type of system also referred to as MPP (Massively Parallel Processing).\u00a0Amazon Redshift<\/b>\u00a0uses 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.\u00a0In this article, we will try to learn\u00a0how to Execute SQL query against Amazon Redshift in SSIS<\/em>.<\/p>\n

    Getting Started<\/span><\/h2>\n

    In order to start, we will show several examples.\u00a0ZappySys includes an\u00a0Amazon Redshift ExecuteSQL Task,\u00a0that will help you\u00a0how 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.<\/p>\n

    Execute SQL query against Amazon Redshift in SSIS<\/h2>\n

    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.<\/p>\n

      \n
    1. 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.<\/li>\n
    2. Once you finished the first step, Open Visual Studio and Create New SSIS Package Project.<\/li>\n
    3. In\u00a0your SSIS toolbox, you should see many new Icons starting with \u201cZS\u201d (i.e. ZappySys). If you don\u2019t see them then\u00a0check this.<\/a><\/li>\n
    4. Here, In Visual Studio, drag and drop the Amazon Redshift ExecuteSQL Task in the design surface.\n
      \"SSIS<\/a>

      SSIS ZS Amazon Redshift ExecuteSQL Task – Drag and Drop<\/p><\/div><\/li>\n

    5. Now we need Amazon Redshift Connection. Click here<\/a> to Create Amazon Redshift Connection.<\/li>\n
    6. Double click on Amazon Redshift ExecuteSQL Task to configure it.<\/li>\n
    7. 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.\n
      DROP TABLE IF EXISTS CustomerData;\r\nCREATE TABLE CustomerData(\r\n\tId bigint NULL,\r\n\tCustomerID VARCHAR(5) NOT NULL,\r\n\tCustomerName VARCHAR(50) NOT NULL,\r\n\tContactName VARCHAR(40) NOT NULL,\r\n\tCustomerCity VARCHAR(50) NOT NULL\r\n)<\/pre>\n
      \"Execute<\/a>
      SSIS Amazon Redshift ExecuteSQL Task – Configure<\/p><\/div><\/li>\n
    8. Click on OK button to save configure setting UI.<\/li>\n
    9. That’s all,\u00a0right-click on the task and run or Execute. This should create a new table in Redshift.\n
      \"SSIS<\/a>
      Run or Execute\u00a0SSIS ZS ExecuteSQL Task Package<\/p><\/div><\/li>\n<\/ol>\n
      Conclusion<\/h2>\n
      After all, In this blog, we learned how to Execute SQL query against Amazon Redshift in SSIS using ZS Amazon Redshift ExecuteSQL Task.\u00a0You can\u00a0download SSIS PowerPack here<\/a>\u00a0to try many other scenarios not discussed in this blog along with 70+ other components.<\/p>\n
      References<\/h2>\n
      Finally, you can use the following links for more information:<\/p>\n