{"id":7380,"date":"2019-07-10T12:45:13","date_gmt":"2019-07-10T12:45:13","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7380"},"modified":"2022-05-02T12:01:01","modified_gmt":"2022-05-02T12:01:01","slug":"read-walmart-api-data-ssis-odbc","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-walmart-api-data-ssis-odbc\/","title":{"rendered":"How to read Walmart API data in SSIS \/ ODBC"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"\"In this article, we will see how to read Walmart API data in SSIS and load into SQL Server.\u00a0This blog mainly focuses on SSIS approach but steps mentioned to call Walmart APIs can be useful for any developer regardless of which programming language or toolset you use.<\/p>\n

Are you a Walmart.com Supplier or Marketplace Seller? Then you may need to\u00a0click here<\/a>\u00a0to visit the site that is focused on these profiles.<\/p>\n

 <\/p>\n

Prerequisites<\/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. SSIS<\/abbr>\u00a0designer installed.\u00a0Sometimes it is referred to as\u00a0BIDS<\/abbr>\u00a0or\u00a0SSDT<\/abbr>\u00a0(download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a>\u00a0is installed (download it<\/a>).<\/li>\n
  4. Credentials of Walmart API. Click here<\/a> to create API.<\/li>\n<\/ol>\n

    Components Mentioned in this article<\/h2>\n
    \n\n\n\n\n
    <\/td>\nZS Logging Task<\/a><\/td>\n<\/tr>\n
    \"\"<\/a><\/td>\nJSON Source(REST API or File)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    What is Walmart?<\/span><\/h2>\n

    Walmart is the world’s largest retailer, and the Walmart Open API provides access to our extensive product catalog, thus enabling digital distribution partners to earn substantial affiliate revenues from customer referrals.<\/p>\n

    Read Walmart API Data using SSIS JSON Source<\/span><\/h2>\n

    Let\u2019s start with an example. We use SSIS JSON Source component to make the call to Walmart API, we will read Walmart data and load into SQL Server.\u00a0First of All, Open Visual Studio and Create New SSIS Package Project.<\/p>\n

    Get\u00a0Dynamic Token (Configure ZS-HTTP Connection)<\/span><\/h3>\n

    To get API response data using SOAP \/ REST API call, you\u00a0need\u00a0to get an API Token from your\u00a0login request URL. Follow the steps mentioned below to get API Token:<\/p>\n

      \n
    1. Now, Right Click on Connection Managers Window, and one Context menu appears for creating a new\u00a0connection manager.\n
      \"Create<\/a>

      Create a New HTTP Connection.<\/p><\/div><\/li>\n

    2. Select ZS HTTP Connection Manager from the list of Connection Managers and, double-click on it or click on Add button to add a new Connection Manager. By Default, The created connection manager is of Package-Level. You can set Project Level Connection manager too If you want.\n
      \"Select<\/a>

      Select ZS-HTTP Connection from SSIS Connection Manager.<\/p><\/div><\/li>\n

    3. In the SSIS ZS HTTP Connection Manager, Go to General Tab and Configure HTTP Connection Manager. Enter API URL, Set Credentials Type to Dynamic Token – Two-Step Auth, and configure some basic settings for the same. We used Login URL as just example but you can enter any valid API URL endpoint here. Actual Login URL goes on Dynamic Token Tab (see next step).\n
      https:\/\/marketplace.walmartapis.com\/v3\/items?nextCursor=*&offset=2000&limit=20<\/pre>\n
      WM_SEC.ACCESS_TOKEN<\/pre>\n
      \"ZS<\/a>
      ZS HTTP Connection – Configure General Tab<\/p><\/div><\/li>\n
    4. Now, In the Dynamic Token Tab do configure like below image. Please click here<\/a> to refer about Walmart API Token.\n
      https:\/\/marketplace.walmartapis.com\/v3\/token<\/pre>\n
      grant_type=client_credentials<\/pre>\n
      Accept: application\/json\r\nCache-Control: no-cache\r\nWM_SVC.NAME: Walmart Marketplace\r\nWM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>>\r\nAuthorization: Basic <<[$userid$]:[$password$],FUN_BASE64ENC>><\/pre>\n
      \"ZS<\/a>
      ZS HTTP Connection – Dynamic Token Tab<\/p><\/div><\/li>\n
    5. On the Response Settings<\/strong> tab configure as below\n
      \"Walmart<\/a>
      Walmart API – Extract Token – Response Settings<\/p><\/div>\n
        \n
      1. Extract Mode = Extract from Body <\/strong><\/li>\n
      2. Expression Type = Json<\/strong><\/li>\n
      3. Response Content Filter = $.access_token<\/span><\/li>\n<\/ol>\n<\/li>\n
      4. Click on OK button to save HTTP Connection configure settings.<\/li>\n<\/ol>\n
        That\u2019s it we have successfully configured Connection for Walmart API in SSIS. In the next section, we will see how to use this connection and read various data from Walmart API.<\/p>\n
        Read Walmart API Data in SSIS<\/span><\/h3>\n
        Once we have done creating HTTP Connection Manager we can move forward to read Walmart API data inside Data Flow. So lets Configure SSIS JSON \/ REST API Source.<\/p>\n
          \n
        1. Now, Drag and drop Data Flow Task from SSIS Toolbox in the currently open project and double click it to edit.
          \n<\/p>\n
          \"Drag<\/a>
          Drag and Drop Data Flow Task.<\/p><\/div><\/li>\n
        2. Double click on the Data Flow task to see the Data Flow designer surface.<\/li>\n
        3. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.\n
          \"Drag<\/a>
          Drag and Drop JSON Source(REST API or File).<\/p><\/div><\/li>\n
        4. Double click JSON Source and enter the following URL as below.\n
          https:\/\/marketplace.walmartapis.com\/v3\/items?nextCursor=*&offset=2000&limit=20<\/pre>\n<\/li>\n
        5. Check Use Credentials and select existing HTTP connection we have created already it.<\/li>\n
        6. Enter Following HTTP Headers. If you prefer Raw edit .. Just click on Raw Edit (#6 in screenshot) and paste below headers. Make sure to replace\u00a0YourClientIdGoesHere<\/strong> and\u00a0YourSecretGoesHere<\/strong>\n
          Content-Type: application\/xml\r\nAccept: application\/xml\r\nAuthorization: Basic <<YourClientIdGoesHere:YourSecretGoesHere,FUN_BASE64ENC>>\r\nWM_SVC.NAME: Walmart Marketplace\r\nWM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>><\/pre>\n
           <\/li>\n
        7. Select Array Filter (ItemResponse node) or type $.ItemResponse[*]\u00a0 as below.\n
        8. That’s it, you are ready to load Wallmart API Data into SQL Server and you can use more ZappySys Destination<\/a> components.<\/li>\n
        9. For pagination call please click here<\/a> for reference to this link.<\/li>\n<\/ol>\n
          Load Walmart API data into SQL Server<\/h3>\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