{"id":7771,"date":"2019-08-16T14:05:24","date_gmt":"2019-08-16T14:05:24","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7771"},"modified":"2019-10-01T11:22:30","modified_gmt":"2019-10-01T11:22:30","slug":"read-write-amazon-redshift-data-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-write-amazon-redshift-data-ssis\/","title":{"rendered":"How to Read \/ Write Amazon Redshift Data in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>ZappySys provide high-performance drag and drop connectors for Amazon Redshift Integration. In our previous post we saw how to\u00a0bulk load SQL data into Redshift using S3 staging technique (COPY command)<\/a>. Now in this post, you will see How to Read \/ Write Amazon Redshift Data in SSIS Data flow without need for S3 Staging. We will use\u00a0 ZS Amazon Redshift components (Source \/ Destination). If you need to Upsert \/ delete data from Redshift Table then use Upsert Destination<\/a> (Not discussed in this article).<\/p>\n

ZappySys developed many AWS related components but in this article, we will look at 2 Components for Amazon Redshift Integration Scenarios (Read and Write).\u00a0We will discuss how to use SSIS Amazon Redshift Source<\/a> and\u00a0SSIS Amazon Redshift Destination.<\/a>\u00a0Amazon Redshift Source\u00a0connector supports familiar\u00a0SQL Like query language<\/strong>\u00a0so any DBA or SQL user can learn it in no time.\u00a0ZappySys developed a highly sophisticated engine to turn your SQL Query into native Amazon Redshift query.\u00a0Load\u00a0query at runtime from the variables or Direct value.<\/p>\n

Prerequisite<\/span><\/h2>\n

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:<\/p>\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<\/span><\/h2>\n
    \n\n\n\n\n
    \"\"<\/a><\/td>\nAmazon Redshift Source<\/a><\/td>\n<\/tr>\n
    \"\"<\/a><\/td>\nAmazon Redshift Destination<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    What is Amazon Redshift<\/span><\/h2>\n

    Before we talk How to Read \/ Write Amazon Redshift Data in SSIS<\/strong>\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 PostgreSQL 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 How to Read \/ Write Amazon Redshift Data in SSIS.<\/em><\/p>\n

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

    In order to start, we will show several examples.\u00a0ZappySys includes an\u00a0Amazon Redshift Source and Destination,\u00a0that will help you How to Read \/ Write Amazon Redshift Data in SSIS. You can connect to your Amazon Redshift Account by entering credentials.<\/p>\n

    SSIS Amazon Redshift Source Connector<\/em>\u00a0can be used to read data from Amazon Redshift. You can use a simple Table mode or write custom SQL Query to extract desired data. You can use multiple\u00a0modes (i.e. Table or Query) to read data from the Amazon Redshift Database. Support for data preview and max rows and\u00a0Dynamic query (using SSIS Variable placeholder e.g. select * from mytable where id='{{User:: MyVar}}’).<\/p>\n

    SSIS Amazon Redshift Destination can be used to bulk insert a large number of records to Amazon Redshift storage from any data source using SSIS.\u00a0Amazon Redshift<\/a>\u00a0is a cloud-based Data Warehouse service from Amazon AWS. Here, you can bulk\u00a0Insert, Update and Delete Amazon Redshift data and supporting the batch size.<\/p>\n

    Click here to see Amazon Redshift<\/a>\u00a0related articles.<\/p>\n

    Write Data using Amazon Redshift Destination in SSIS<\/h2>\n

    Overview<\/h3>\n

    In this tutorial, you will learn how to write data in Amazon Redshift Database. You can use\u00a0ZS DummyData Source<\/a>\u00a0for practice it or select suitable ZS Source Adapter<\/a> (In this case it\u2019s from SQL Server Source). So, Let\u2019s start with an example. In this SSIS we will write data into Amazon Redshift Database using ZS Amazon Redshift Destination.<\/p>\n

    How to write data into Amazon Redshift Table<\/h3>\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. For this sample exercise, we will create a table in Redshift.\n
      CREATE TABLE public.\"tbl_pg_Customers\"\r\n(\r\n    \"RecordID\" character varying(50) NOT NULL,\r\n    \"CustomerID\" character varying(50),\r\n    \"CustomerName\" character varying(50),\r\n    \"Address1\" character varying(50),\r\n    \"City\" character varying(50),\r\n    \"State\" character varying(50),\r\n    \"Zip\" character varying(50),\r\n    \"Country\" character varying(50),\r\n    PRIMARY KEY (\"RecordID\")\r\n);<\/pre>\n<\/li>\n
    3. Once you have created a Table, you may proceed to create a new SSIS package. In BIDS\/SSDT create a new SSIS package.<\/li>\n
    4. 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
    5. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.\n
    6. Double click on the Data Flow Task to see Data flow designer surface.<\/li>\n
    7. Here, In Visual Studio, Drag and Drop the OLE DB Source and Amazon Redshift Destination in the design surface and join the components with the blue arrow.\n
      \"SSIS<\/a>
      SSIS OLE DB Source and Amazon Redshift Destination – Drag and Drop<\/p><\/div><\/li>\n
    8. Now, we need OLE DB Connection and Amazon Redshift Connection. Click\u00a0here<\/a>\u00a0for Create Amazon Redshift Connection.<\/li>\n<\/ol>\n
      How to Create an OLE DB Connection.<\/h3>\n
        \n
      1. Let’s, Right-click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.\n
        \"SSIS<\/a>
        SSIS OLE DB Connection – Create Connection<\/p><\/div><\/li>\n
      2. Now click on the New button to create a connection.\n
        \"SSIS<\/a>
        SSIS OLE DB Connection – Create New Connection<\/p><\/div><\/li>\n
      3. Let’s configure connection Manager, just follow the steps one as we have created in following the image.\n
        \"SSIS<\/a>
        SSIS OLE DB Connection – Configure Connection<\/p><\/div><\/li>\n
      4. After all, Click on OK button to save configure setting UI.<\/li>\n<\/ol>\n
        Configure SSIS ZS Amazon Redshift Destination<\/h3>\n
          \n
        1. Double click on OLE DB Source for configure it.<\/li>\n
        2. Let’s Configure in Connection Manager, just follow below image steps.\n
          \"SSIS<\/a>
          SSIS OLE DB Source – Configure Source Editors<\/p><\/div><\/li>\n
        3. Click OK to save OLE DB Source Editor UI settings.<\/li>\n
        4. Now double click on Amazon Redshift Destination to configure it. In the [Connection Manager] tab Select Amazon Redshift Connection.\n
          \"SSIS<\/a>
          SSIS Amazon Redshift Destination – Connection Manager<\/p><\/div><\/li>\n
        5. Click on [Component Properties] tab and Pick the Destination Table from Tables dropdown list.\n
          \"SSIS<\/a>
          SSIS Amazon Redshift Destination – Component Properties<\/p><\/div><\/li>\n
        6. Click on [Column mappings] tab and verify mapping. If the target table is new Table and it’s empty then all input columns will be automatically mapped as shown below. If the table is an existing table with some data then you can manually pick a mapping column by dragging it from source list to target list.\n
          \"SSIS<\/a>
          SSIS Amazon Redshift Destination – Column Mappings<\/p><\/div><\/li>\n
        7. Execute the package and verify that the data is saved.\n
          \"SSIS<\/a>
          SSIS Amazon Redshift Destination – Run or Execute Package<\/p><\/div><\/li>\n<\/ol>\n
          Read Data using Amazon Redshift Source in SSIS<\/h2>\n
          Overview<\/h3>\n
          In this tutorial, you will learn how to read data from Amazon Redshift in SSIS using ZS Amazon Redshift Source adapter. In this SSIS we will read data with SQL Query and Table Mode. So Let\u2019s begin it.<\/p>\n
          How to Read data from Amazon Redshift Table<\/h3>\n
            \n
          1. You may proceed to create a new SSIS package. In BIDS\/SSDT create a new SSIS package.<\/li>\n
          2. From the SSIS toolbox drag and drop Data Flow Task on the control flow designer surface.\n
            \"SSIS<\/a>
            SSIS Data Flow Task – Drag and Drop<\/p><\/div><\/li>\n
          3. Double click on the Data Flow Task to see the Data Flow design surface.<\/li>\n
          4. From the SSIS Toolbox, Drag and Drop Amazon Redshift Source in the data flow designer surface.\n
            \"SSIS<\/a>
            SSIS Amazon Redshift Source – Drag and Drop<\/p><\/div><\/li>\n
          5. Now, We need Amazon Redshift Connection. Click here<\/a> for creating Amazon Redshift Connection.<\/li>\n<\/ol>\n
            Configure SSIS ZS Amazon Redshift Source – Query Mode<\/h4>\n
              \n
            1. Here, we are getting data using Query Mode with dynamic expression value.<\/li>\n
            2. Let’s Create Variables, In Visual Studio Right Click on Design Panel, Select Variables.\n
              \"SSIS<\/a>
              SSIS Create Variables<\/p><\/div><\/li>\n
            3. Make sure correct DataType and Value, use the following image.\n
              \"SSIS<\/a>
              SSIS Created Variables with Value<\/p><\/div><\/li>\n
            4. Double click on Amazon Redshift Source to configure it.<\/li>\n
            5. Select the connection we have created before, set AccessMode to Query and use the following SQL Query(You can replace table or column name) and insert Variables we have created before.\n
              SELECT  * FROM \"public\".\"tbl_pg_Orders\"\r\nWHERE \"CustomerID\" like '%R%' AND \"OrderAmount\" > '{{User::OrderAmount}}' AND\r\n\"{{User::ColumnName}}\" = '{{User::Value}}'<\/pre>\n
              \"SSIS<\/a>
              SSIS Amazon Redshift Source – Configure with SQL Query Mode<\/p><\/div><\/li>\n
            6. Click on Preview button to see Data Preview and OK button to save configure setting UI.<\/li>\n
            7. Here, you can use any of our suitable ZS Destination Adapter.<\/a><\/li>\n
            8. From the SSIS toolbox drag and drop Trash Destination on the Data Flow designer surface.\n
              \"SSIS<\/a>
              SSIS Trash Destination – Drag and Drop<\/p><\/div><\/li>\n
            9. Now single click on the Amazon Redshift Source, once you see the blue arrow from the source…connect it to Trash Destination.<\/li>\n
            10. Double click on Trash Destination to Configure it.\n
              \"SSIS<\/a>
              SSIS Trash Destination – Configure<\/p><\/div><\/li>\n
            11. Click on OK button to save Trash Destination configure setting UI.<\/li>\n
            12. That’s all, just Run or Execute the package and verify data.\n
              \"SSIS<\/a>
              SSIS Amazon Redshift Source – Run or Execute the Package<\/p><\/div><\/li>\n<\/ol>\n
              Configure SSIS ZS Amazon Redshift Source – Table Mode<\/h4>\n
                \n
              1. Double click on Amazon Redshift Source to configure it.<\/li>\n
              2. Select the connection we have created before. Set Access Mode to Table and Select Table from table Drop down list.\n
                \"SSIS<\/a>
                SSIS Amazon Redshift Source – Configure with Table Mode<\/p><\/div><\/li>\n
              3. Click on Preview button to see Data Preview and OK button to save Amazon Redshift Source configure setting UI.<\/li>\n
              4. From the SSIS toolbox, Drag and Drop Trash Destination on the Data flow designer surface.\n
                \"SSIS<\/a>
                SSIS Trash Destination – Drag and Drop<\/p><\/div><\/li>\n
              5. Now single click on the Amazon Redshift Source, once you see the blue arrow from a source… connect it to Trash Destination.<\/li>\n
              6. Double click on ZS Trash Destination to configure it.\n
                \"SSIS<\/a>
                SSIS Trash Destination – Configure<\/p><\/div><\/li>\n
              7. Click on OK button to save Trash Destination configure setting UI.<\/li>\n
              8. That’s all, just run or execute your package and see data.\n
                \"SSIS<\/a>
                SSIS Amazon Redshift Source – Run or Execute Package<\/p><\/div><\/li>\n<\/ol>\n
                Conclusion<\/h2>\n
                In this blog, we learn\u00a0How to Read \/ Write Amazon Redshift Data in SSIS. We used Amazon Redshift Destination for write data and Amazon Redshift Source to read data. You 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