How to read Walmart API data in SSIS / ODBC

Introduction

In this article, we will see how to read Walmart API data in SSIS and load into SQL Server. This 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.

Are you a Walmart.com Supplier or Marketplace Seller? Then you may need to click here to visit the site that is focused on these profiles.

 

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:

  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Credentials of Walmart API. Click here to create API.

Components Mentioned in this article

What is Walmart?

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.

Read Walmart API Data using SSIS JSON Source

Let’s 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. First of All, Open Visual Studio and Create New SSIS Package Project.

How to encrypt Authorization Token as Base64Encode

  1. Here we need to pass Authorization header Token as Base64Encode(clientId:clientSecret) encrypted so I already encrypted it using ZS Logging task and used it here. Please refer to this link for the same.
  2. In visual studio just Drag and Drop ZS Logging Task in the design panel.
    Drag and Drop ZS Logging Task

    Drag and Drop ZS Logging Task

  3. Double click on ZS Logging Task to configure it.
  4. In the ZS Logging Task configure like the following image.
    Logging Task : Base64Enocde

    Logging Task : Base64Enocde

  5. That’s all, Save Base64Encoded token for later use in HTTP Connection.

Get Dynamic Token (Configure ZS-HTTP Connection)

To get API response data using SOAP / REST API call, you need to get an API Token from your login request URL. Follow the steps mentioned below to get API Token:

  1. Now, Right Click on Connection Managers Window, and one Context menu appears for creating a new connection manager.
    Create a New HTTP Connection.

    Create a New HTTP Connection.

  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.
    Select ZS-HTTP Connection from SSIS Connection Manager.

    Select ZS-HTTP Connection from SSIS Connection Manager.

  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).
    ZS HTTP Connection - Configure General Tab

    ZS HTTP Connection – Configure General Tab

  4. Now, In the Dynamic Token Tab do configure like below image. Please click here to refer about Walmart API Token.
    ZS HTTP Connection - Dynamic Token Tab

    ZS HTTP Connection – Dynamic Token Tab

  5. On the Response Settings tab configure as below
    Walmart API - Extract Token - Response Settings

    Walmart API – Extract Token – Response Settings

    1. Extract Mode = Extract from Body
    2. Expression Type = Json
    3. Response Content Filter = $.access_token
  6. Click on OK button to save HTTP Connection configure settings.

That’s 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.

Read Walmart API Data in SSIS

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.

  1. Now, Drag and drop Data Flow Task from SSIS Toolbox in the currently open project and double click it to edit.

    Drag and Drop Data Flow Task.

    Drag and Drop Data Flow Task.

  2. Double click on the Data Flow task to see the Data Flow designer surface.
  3. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
    Drag and Drop JSON Source(REST API or File).

    Drag and Drop JSON Source(REST API or File).

  4. Double click JSON Source and enter the following URL as below.
  5. Check Use Credentials and select existing HTTP connection we have created already it.
  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 YourClientIdGoesHere and YourSecretGoesHere
     
  7. Select Array Filter (ItemResponse node) or type $.ItemResponse[*]  as below.
    ZS JSON Source - Configure

    ZS JSON Source – Configure

  8. That’s it, you are ready to load Wallmart API Data into SQL Server and you can use more ZappySys Destination components.
  9. For pagination call please click here for reference to this link.

Load Walmart API data into SQL Server

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
  2. Connect our Source component to OLEDB Destination
  3. Double click OLEDB Destination to configure it
  4. Select Target Connection or click NEW to create new connection
    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
    Create NEW Table SSIS OLEDB Destination

    Create NEW Table SSIS OLEDB Destination

  7. Click OK to Save OLEDB Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

Walmart API integration using ODBC Driver (Power BI, Excel, MS Access)

So far we have seen how to access Walmart API using SSIS. However there will be a time when you like to access API data inside other Apps using ODBC Driver. ZappySys Provides JSON/XML/CSV drivers for virtually any API including Walmart API. Here is how to access Walmart API using XML ODBC Driver.

  1. Type “ODBC” in Start menu. Open ODBC Data sources (64 bit)
    Open ODBC Data Sources from Startup menu
  2. Click Add on User DSN tab (if you need to use DSN under different Account …i.e. Service Account then click on System DSN Tab)
  3. Select ZappySys XML Driver
  4. When New DSN UI shows up, Change DSN name to anything you like (e.g. WalmartAPI)
  5. Click on Load ConnectionString button and enter below connection. Make sure to replace few things
    First ######YourClientId####### with your own clientid
    Second ######YourSecret####### with your own secret
    Third ######Base64OfClientIdAndSecret####### with base64 encoded string of clientId:Secret    (both separated with colon). You can use any site like this one to generate base64 string of your clientid and secret (seperated by “:”)
    ZappySys Driver - Load Connection String

    ZappySys Driver – Load Connection String

     

  6. Now go to preview tab and enter below query and click Run below SQL.

    –OR–
    NOTE: Change YourClientId and YourSecret in below sql before running

     

Walmart API Integration with Other BI Tools (Power BI, Excel, SSRS, MS Access…)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.   ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools Integration

ETL Tools Integration Programming Languages Integration
 

Conclusion

After all, we saw you how to extract information from REST API such as Walmart REST API using HTTP Connection and load into SQL Server. We also learned techniques like How to get Dynamic Token using HTTP Connection. To explore many other scenarios not discussed in this article download SSIS PowerPack from here (includes 70+ Components).

References

Finally, you can use the following links for more information:

Posted in ODBC PowerPack, REST API, SSIS JSON Source (File/REST), SSIS Logging Task, XML File / SOAP API Driver and tagged , , , , , , , , , , , .