{"id":5267,"date":"2018-11-01T16:43:04","date_gmt":"2018-11-01T16:43:04","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5267"},"modified":"2019-09-17T16:03:08","modified_gmt":"2019-09-17T16:03:08","slug":"ssis-magento-data-read-write-using-rest-api-call","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-magento-data-read-write-using-rest-api-call\/","title":{"rendered":"SSIS Magento data Read \/ Write using REST API Call"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In this post we will lean SSIS Magento data read \/ write operations. Magento is a very popular eCommerce platform and they offer JSON based REST API<\/strong>\u00a0and XML based SOAP API<\/strong>. You can use either API based on your need to automate common integration needs.<\/p>\n

We recommend using REST API (JSON API) if possible because they are simpler and faster. We will focus only on JSON API in this article but we do have blog post on how to call SOAP API<\/a>.<\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    About Calling Magento REST API<\/h2>\n

    If you are new to Magento API we highly recommend to get familiar with Magento REST API here<\/a>. Magento REST API\u00a0 offers two ways to authenticate.<\/p>\n

      \n
    1. Token based Authentication<\/a><\/li>\n
    2. OAuth Based Authentication (OAuth 1.0a)<\/a><\/li>\n<\/ol>\n

      In this article we will look at only Token based approach. In token based approach we have to get new token using admin \/ customer account information.<\/p>\n

      We will cover later how to access admin or customer level API later in this article (See HTTP connection configuration).<\/p>\n

      Configure Connection \/ Call Magento REST API in SSIS<\/h2>\n

      Now lets look at very simple example how to use Token based method to call Magento REST API in SSIS. We will first configure HTTP connection and then call REST API.<\/p>\n

      Let’s get started.<\/p>\n

        \n
      1. First, open SSIS Package after installing SSIS PowerPack<\/a><\/li>\n
      2. Drag ZS REST API Task<\/a> from SSIS Toolbox on Control Flow Designer. Let’s rename task to Get Magento Categories<\/strong><\/li>\n
      3. Enter REST API URL you like to call. For example we can use below URL. Replace **your-magento-host**<\/strong> with your own name.\n
        http:\/\/**your-magento-host**\/index.php\/rest\/V1\/categories<\/pre>\n<\/li>\n
      4. Select Url from Connection<\/strong> mode<\/li>\n
      5. From Connection Dropdown select new ZS-HTTP<\/strong> connection\n
      6. Configure General Tab of HTTP Connection like below
        \nURL can be anything for now because we will use override option in next step (On REST API Task).<\/li>\n
      7. Select Credentials Type as Dynamic Token<\/a>.<\/li>\n
      8. Enter Userid \/ password\n
      9. Now goto Dynamic Token Tab (Or click Configure<\/strong> Link) and configure like below.<\/li>\n
      10. Enter Token URL for API Login URL field.
        \nNOTE<\/strong>: If you are a customer and want to access your own account (e.g. view your orders) then replace \/admin<\/strong> with \/customer<\/strong>\u00a0 in URL)
        \nReplace **my-host**<\/strong> with your host name.<\/p>\n
        http:\/\/***my-host***\/index.php\/rest\/V1\/integration\/admin\/token<\/pre>\n<\/li>\n
      11. Change Method to POST.<\/li>\n
      12. Enter Body as below (Placeholders are automatically replaced when \/token endpoint is called.\n
        {\"username\":\"[$userid$]\", \"password\":\"[$password$]\"}<\/pre>\n<\/li>\n
      13. Select Content Type as Application\/Json\n
      14. Now click Dynamic Token – Response Tab and configure like below.
        \nSelect Regex<\/strong> and enter Expression as below. This will remove double quotes around token in response.<\/p>\n
        \"(.*)\"{{0,1}}<\/pre>\n
        \"Extract<\/a>
        Extract Token from response – Remove double quotes around value using Regular Expression<\/p><\/div><\/li>\n
      15. Click OK to save connection<\/li>\n
      16. Now on REST API Task check Use Direct URL<\/strong> Option<\/li>\n
      17. Click Test Request to confirm its working\n
        \"Call<\/a>
        Call Magento REST API using SSIS REST API Task<\/p><\/div><\/li>\n<\/ol>\n
         <\/p>\n
        Read data from Magento and Load into SQL Server<\/h2>\n
        Now lets look at how to read Magento data and load into SQL Server. Assume that you like to read all products.<\/p>\n
        Configure JSON Source (Read Magento REST API)<\/h3>\n
          \n
        1. Drag Data flow and double click to edit:\n
          \"\"
          Dragging and dropping Data Flow Task into Control Flow<\/p><\/div><\/li>\n
        2. Drag ZS JSON Source<\/a> on data flow and double click to edit<\/li>\n
        3. Configure JSON Source like below if you like to get product list for example\n
            \n
          1. Enter URL as below (change http to https if your host support secure channel).\n
            http:\/\/***yourhost***\/index.php\/rest\/V1\/products?searchCriteria[pageSize]=1000\r\n--- to extract only specific fields do below way --\r\nhttp:\/\/***yourhost***\/index.php\/rest\/V1\/products?searchCriteria[pageSize]=1000&fields=items[sku,name]<\/pre>\n<\/li>\n
          2. Check Use Credentials. Use same HTTP connection we created in previous section<\/li>\n
          3. In the Filter enter $.items[*]<\/strong> or click Select Filter to Browse and select items node.<\/li>\n
          4. Click Preview to confirm<\/li>\n<\/ol>\n<\/li>\n
          5. Here is how your setup will look like after configuration.\n
          6. If you expecting more than 1000 rows then you have to set up pagination. Now go to pagination tab<\/strong> and configure as below (Below options are only available in v2.9.1 or higher<\/a>). If you have older version then check next section for workaround.\n
              \n
            1. Pagination Mode =URL Parameter Mode<\/strong><\/li>\n
            2. Page Num Indicator =\u00a0searchCriteria[currentPage]<\/strong><\/li>\n
            3. Max Rows Expression =\u00a0$.total_count<\/strong><\/li>\n
            4. Page Data Expression =\u00a0$.items[*]<\/strong><\/li>\n<\/ol>\n<\/li>\n
            5. Here is how your Magento REST API Pagination Setup will look like.\n
            6. Click OK to save JSON Source. In next section we will see how to load data into Target like SQL Server.<\/li>\n<\/ol>\n
              Configure OLEDB Destination (Load into SQL Server)<\/h3>\n
                \n
              1. Drag OLEDB Destination.<\/li>\n
              2. Select \/ Create Connection<\/li>\n
              3. Select or create NEW target Table (Click NEW button)<\/li>\n
              4. Click Mapping Tab to map source columns to target table<\/li>\n
              5. Click OK to save<\/li>\n
              6. Execute Package<\/li>\n<\/ol>\n
                ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
                Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
                  \r\n
                1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
                2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
                  \r\n\r\n\"\"<\/a>\r\n
                  SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
                  Step 2: Configure Target Connection<\/h3>\r\n
                    \r\n
                  1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
                  2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n