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:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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.
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:
- Now, Right Click on Connection Managers Window, and one Context menu appears for creating a new connection manager.
Create a New HTTP Connection.
- 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.
- 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).
1https://marketplace.walmartapis.com/v3/items?nextCursor=*&offset=2000&limit=201WM_SEC.ACCESS_TOKEN
ZS HTTP Connection – Configure General Tab
- Now, In the Dynamic Token Tab do configure like below image. Please click here to refer about Walmart API Token.
1https://marketplace.walmartapis.com/v3/token1grant_type=client_credentials12345Accept: application/jsonCache-Control: no-cacheWM_SVC.NAME: Walmart MarketplaceWM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>>Authorization: Basic <<[$userid$]:[$password$],FUN_BASE64ENC>>
ZS HTTP Connection – Dynamic Token Tab
- On the Response Settings tab configure as below
Walmart API – Extract Token – Response Settings
- Extract Mode = Extract from Body
- Expression Type = Json
- Response Content Filter = $.access_token
- 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.
- 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.
- Double click on the Data Flow task to see the Data Flow designer surface.
- From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
Drag and Drop JSON Source(REST API or File).
- Double click JSON Source and enter the following URL as below.
1https://marketplace.walmartapis.com/v3/items?nextCursor=*&offset=2000&limit=20 - Check Use Credentials and select existing HTTP connection we have created already it.
- 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
12345Content-Type: application/xmlAccept: application/xmlAuthorization: Basic <<YourClientIdGoesHere:YourSecretGoesHere,FUN_BASE64ENC>>WM_SVC.NAME: Walmart MarketplaceWM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>> - Select Array Filter (ItemResponse node) or type $.ItemResponse[*] as below.
ZS JSON Source – Configure
- That’s it, you are ready to load Wallmart API Data into SQL Server and you can use more ZappySys Destination components.
- For pagination call please click here for reference to this link.
Load Walmart API data into SQL Server
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS - Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
SSIS Upsert Destination - Columns Mappings - Click OK to Save Upsert Destination Settings
- 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
- 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.
- Type “ODBC” in Start menu. Open ODBC Data sources (64 bit)
- 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)
- Select ZappySys XML Driver
- When New DSN UI shows up, Change DSN name to anything you like (e.g. WalmartAPI)
- 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 “:”)
12345678910111213141516171819DRIVER={ZappySys XML Driver};ElementsToTreatAsArray='ns2:ItemResponse';DataPath='https://marketplace.walmartapis.com/v3/items?nextCursor=*&offset=2000&limit=20';DataConnectionType=HTTP;AuthScheme='{none}';Url='https://marketplace.walmartapis.com/v3/items?nextCursor=*&offset=2000&limit=20';TokenUrl='https://marketplace.walmartapis.com/v3/token';TokenRequestData='grant_type=client_credentials';TokenRequestMethod='POST';TokenResponseContentFilter='$.access_token';TokenRequestHeaders='Accept:application/json||Cache-Control:no-cache||WM_SVC.NAME:Walmart Marketplace||WM_QOS.CORRELATION_ID:TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>>||Authorization:Basic <<[$userid$]:[$password$],FUN_BASE64ENC>>';TokenAuthHeader='WM_SEC.ACCESS_TOKEN';TokenResponseContentType=Json;UserName='######YourClientId#######';CredentialType=TokenDynamic;Password='######YourSecret#######';Filter='$.ns2:ItemResponses.ns2:ItemResponse[*]';RequestMethod='GET';RequestHeaders='Content-Type: application/xml || Accept: application/xml || Authorization: Basic ######Base64OfClientIdAndSecret####### || WM_SVC.NAME: Walmart Marketplace || WM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>>'
ZappySys Driver – Load Connection String
- Now go to preview tab and enter below query and click Run below SQL.
1select * from $
–OR–
NOTE: Change YourClientId and YourSecret in below sql before running123456789SELECT * FROM $WITH(Src='https://marketplace.walmartapis.com/v3/items?nextCursor=*&offset=2000&limit=20',Header='Content-Type: application/xmlAccept: application/xmlAuthorization: Basic <<YourClientid:YourSecret,FUN_BASE64ENC>>WM_SVC.NAME: Walmart MarketplaceWM_QOS.CORRELATION_ID: TokenId_<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_GETDATE_UTC>>')
Walmart API Integration with Other BI Tools (Power BI, Excel, SSRS, MS Access…)
|
BI / Reporting Tools Integration |
ETL Tools Integration | Programming Languages Integration |
|
|
|
How to encrypt Authorization Token as Base64Encode
- If you want to directly pass Authorization header Token as Base64Encode(clientId:clientSecret) encrypted in the get token call, you can encrypted it using ZS Logging task and used it. Please refer to this link for the same.
- In visual studio just Drag and Drop ZS Logging Task in the design panel.
Drag and Drop ZS Logging Task
- Double click on ZS Logging Task to configure it.
- In the ZS Logging Task configure like the following image.
Logging Task : Base64Enocde
- That’s all, Save Base64Encoded token for use in HTTP Connection.
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:
- Help File: JSON Source(REST API or File), ZS Logging Task
- Walmart API: HTTP Connection
