Call Amazon MWS API using SSIS (Marketplace Web Service)

Introduction

In this post you will learn how to call Amazon MWS API (Amazon Marketplace Web Service) using SSIS PowerPack.

Using drag and drop approach you can consume data from Amazon MWS XML Web service. In this post we will use ZappySys XML Source connector to read data from Amazon MWS API and load into SQL Server. We will also use REST API Task to call any API from Marketplace Web service and save output into Variable.

Assumption

This post assumes following things

  1. You have basic knowledge of SSIS and XML format.
  2. You have Amazon MWS Account and you have obtained valid AWSAccessKeyId and Secret Key to call Amazon MWS API.
  3. You have tested few API calls using Amazon MWS Scratchpad this will give you idea about various API you can call and parameters you need to pass.

Step-By-Step – Call Amazon MWS API using SSIS

To consume data from any XML Source (File, SOAP Web Service or XML API) you can use  ZappySys XML Source
connector.
 or REST API Task. In below example you will see how to call Amazon MWS API and save output into Variable. With this approach you can call any API (GET / POST / DELETE / PUT)

  1. Download and Install SSIS PowerPack
  2. Create test package
  3. From SSIS toolbox drag ZS REST API Task  and double click on the task to configure it
  4. Select Request URL Access Mode = [Url from Connection]
  5. Enter URL as below
    In above URL
    ** mws.amazonservices.com => This is your endpoint. If you are not in USA then Click here to find correct endpoint
    ** /Orders => That’s your API you want to call
    ** /2013-09-01 => That’s your API version (You have to add this same version in your POST Body too (see next section).
  6. In Select Url Connection dropdown select New OAUTH connection option.
  7. When prompted on OAuth connection UI select Provider=Amazon MWS, Enter AWSAccessKey and Secret key and Click OK (If you have MWSAuthToken then enter in Access Token field else leave it empty). Do not test because it may not work yet.
    SSIS OAuth Connection - Call Amazon MWS API (Marketplace Web service API)

    SSIS OAuth Connection – Call Amazon MWS API (Marketplace Web service API)

  8. Now select Method=POST, and in the body enter following
    If your API requires pagination (more than 100 records from response) then refer next section (Sample Package). In the case of Pagination your second request may look like below
    NOTE: Enter all required parameters in the following format. If Value needs to be URL encoded then use SSIS variable along with URLENC format specifier like this {{User::varVal,URLENC}}
    Syntax: param1=value1&param2=value2…….&paramN=valueN
  9. Now click Test and see content in the Preview window.
    SSIS REST API Task - Call Amazon MWS API - Get XML data save into SSIS variable

    SSIS REST API Task – Call Amazon MWS API – Get XML data save into SSIS variable

  10. If you wish to save response data into SSIS variable then goto response Tab and check save option and select variable name.

Paging large response using NextToken

Many Amazon MWS API calls by default don’t return all records. Rather than returning all rows you may get partial response (e.g. Max 50 or 100 rows). If you want to get all records then you have to use NextToken  found in your XML. For API which requires pagination, in that case your POST body may be different for 2nd or higher requests. Notice how we changed Action amd appended NextToken=xxxxxxxxxxx in the second request. Also notice we use Variable for NextToken along with URLENC specifier. This is very important because as per Amazon Specs this value has to be Url Encoded.

First Request (MWS API with Pagination using NextToken)

Second Request or higher (MWS API with Pagination using NextToken)

Sample SSIS Package – Amazon MWS Pagination Example

If you want to see complete working package for pagination pattern then download from here. Change AccessKey, SecretKey, SellerID and Path as needed to make this package work.

Here is the screenshot of example SSIS package for Amazon MWS.

SSIS Example Package - Read data from Amazon MWS Web service (API Call). Paginate API calls using NextToken

SSIS Example Package – Read data from Amazon MWS Web service (API Call). Paginate API calls using NextToken

Load Amazon MWS API data into SQL Server using SSIS

In this section we will see how to use ZappySys XML Source connector to read data from Amazon MWS Web Service and load into SQL Server

For making things simple we are calling

  1. Download and Install SSIS PowerPack
  2. Create test package
  3. From SSIS toolbox drag Data Flow task and double click task to go to Data Flow designer
  4. Drag ZS XML Source from SSIS Toolbox
  5. Configure XML Source as below
    SSIS XML Source - Read data from Amazon MWS API (Amazon Marketplace Web service call)

    SSIS XML Source – Read data from Amazon MWS API (Amazon Marketplace Web service call)

  6. Drag OLEDB destination from SSIS Toolbox.
  7. Connect XML Source to OLEDB Destination and map input column to target table
  8. Execute dataflow
    Get data from Amazon MWS API - Save to File or SQL Server using SSIS

    Get data from Amazon MWS API – Save to File or SQL Server using SSIS

Working with Amazon MWS Feed API (e.g. Upload file)

Amazon MWS Provides Feed APIs which has slight different requirements. Important requirement in Feed file upload is you have to supply Content MD5 Hash. If you use SSIS PowerPack then you don’t have to worry about that complexity because calculating MD5 Hash is automatically done.

Upload Amazon MWS Feed File in TSV / CSV format (Tab separated)

Here is the screenshot for how to upload Tab separated values in CSV file. You can specify body from Variable or Read from file. If you read from file then must check File Upload/Multi-Part option and start path with @ symbol as below.

Call Amazon MWS Feed API - Upload CSV file format (Tab separated values - TSV)  - XML

Call Amazon MWS Feed API – Upload CSV file format (Tab separated values – TSV)

Upload Amazon MWS Feed File in XML format

To upload feed file in XML format use same settings as above except Content Type should be XML (text/xml) (Select in the dropdown)

Conclusion

Amazon Marketplace Web service (MWS) provides great way to automate many functionality for Marketplace Seller. Using ZappySys SSIS PowerPack you con consume data from any REST API or Web service without replying on SDK / coding approach (e.g. C#, Java, Python, Ruby).

Posted in SSIS XML Source (File and tagged , , , , , , , .