How to call Amazon MWS API using SSIS

UPDATE: ZappySys has released a brand new API Connector for Amazon MWS Online which makes it much simpler to Read/Write Amazon MWS Data in SSIS compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

In this post you will learn how to call Amazon MWS API (Amazon Marketplace Web Service) or Amazon Product Advertising API 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.

Prerequisites

This post assumes following things

Basic knowledge of SSIS and XML format.
Amazon MWS Account and and valid AWSAccessKeyId and Secret Key to call Amazon MWS API (How to obtain Key / Secret and SellerId ? ).
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.

Testing MWS Requests using ScratchPad

Very first step we recommend before you call MWS API in SSIS is to get familiar with Amazon MWS Scratchpad tool. Check this article for detailed steps. You can also use Fiddler to debug some requests.

Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) – ListMatchingProducts Example

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)

Download and Install SSIS PowerPack
Create test package
From SSIS toolbox drag ZS REST API Task and double click on the task to configure it
Select Request URL Access Mode = [Url from Connection]
Enter URL as below

https://mws.amazonservices.com/Orders/2013-09-01

1

https://mws.amazonservices.com/Orders/2013-09-01

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).
In Select Url Connection dropdown select New OAUTH connection option.
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)
Another Setting you may turn on is Retry Handling. This allows to over come ThresholdLimitReached error when we call API too fast.
Retry Options
Now select Method=POST, and in the body enter following

Action=GetServiceStatus&SellerId=A77XXXXXXX&Version=2013-09-01

1

Action=GetServiceStatus&SellerId=A77XXXXXXX&Version=2013-09-01

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

Action=YourActionNameWithNextToken&SellerId=A77XXXXXXX&Version=2013-09-01&NextToken={{User::vNextToken,FUN_URLENC}}

1

Action=YourActionNameWithNextToken&SellerId=A77XXXXXXX&Version=2013-09-01&NextToken={{User::vNextToken,FUN_URLENC}}

NOTE: Enter all required parameters in the following format, as shown below. All values need to be URL-encoded. If you use SSIS variable, use it along with FUN_URLENC format specifier, e.g. {{User::myVariable,FUN_URLENC}}. If you use a hard-coded value, and not sure if it is URL-encoded, then encode it with the same FUN_URLENC format specifier/function, e.g. <<myValue,FUN_URLENC>>. Always check if SSIS configuration matches the one you see in MWS ScratchPad Request Details.
Format:
param1=value1&param2=<<value2,FUN_URLENC>>&param3={{User::myVar,FUN_URLENC}}
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
If you wish to save response data into SSIS variable then goto response Tab and check save option and select variable name.

Amazon MWS API Pagination

Many Amazon MWS API calls by default don’t return all records (Explained here). 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 and appended NextToken=xxxxxxxxxxx in the second request. Also notice we used encoded token (URL encoded). This is very important because as per Amazon Specs this value has to be Url Encoded.

We also documented similar use case of Amazon MWS Pagination for Power BI (Check this one) its mostly same setup except 2-3 properties might be named different way.

Now lets look at sample request / response for paginated MWS requests (For clarity we have removed many common Parameters which are automatically added at runtime e.g. Signature, Timestamp). We use ListOrders (First Request) and ListOrderByNextToken (Second+ requests)

NOTE: Most of Amazon MWS API calls are heavily throttled means you cannot call more than X calls per minute or hour. So Refer to Throttling Limits for each API Endpoint.

First Request

POST https://mws.amazonservices.com/Orders/2013-09-01

Action=ListOrders&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z

First Response

<?xml version="1.0"?>

<ListOrdersResponse xmlns="https://mws.amazonservices.com/

Orders/2013-09-01">

<NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=</NextToken>

</Orders>

</ListOrdersResult>

</ListOrdersResponse>

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

POST https://mws.amazonservices.com/Orders/2013-09-01

Action=ListOrdersByNextToken&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z&NextToken=YOUR_ENCODED_TOKEN_FROM_PREV_REQUEST

Second or higher Response

<?xml version="1.0"?>

<ListOrdersByNextTokenResponse xmlns="https://mws.amazonservices.com/

Orders/2013-09-01">

<NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=</NextToken>

</Orders>

</ListOrdersResult>

</ListOrdersResponse>

Sample SSIS Package

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.

Amazon MWS APi Pagination (ListOrders , ListOrdersByNextToken Example)

Copy/Paste Properties

Here is copy/paste values for properties listed above.

DirectPath: https://mws.amazonservices.com/Orders/2013-09-01

EnablePageTokenForBody: True

Filter: $.ListOrders[$tag$]Response.ListOrders[$tag$]Result.Orders.Order[*]

HasDifferentNextPageInfo: True

HttpRequestData: Action=ListOrders[$tag$]&SellerId=A1xxxxxxxx&CreatedAfter=2018-09-01T04%3A00%3A00Z&MarketplaceId.Id.1=ATVPDKIKX0DER

HttpRequestMethod: POST

NextUrlAttribute: $.ListOrders[$tag$]Response.ListOrders[$tag$]Result.NextToken

NextUrlSuffix: &NextToken=<%nextlink_encoded%>

NetUrlWait: 10000 (ms) -- slow down to avoid throttling ... Max 6 requests per minute, increase if you get API Limit Error

PagePlaceholders: body=|ByNextToken;filter=|ByNextToken

Understanding Pagination Properties

Here are various properties you have to set for Pagination.

Property

Description

Src

Change this to correct URL. Look at Scratchpad Response Details Tab and Findout first line after POST and before “?” . e.g. /Orders/2013-09-01 this will be used in your URL.
Example:
https://mws.amazonservices.com/Orders/2013-09-01

Body (i.e. RequestData)

Scratchpad shows HTTP POST section. You can copy that and remove System supplied parameters and then arrange all in one line to form your Body for request.

Example:

POST /Orders/2013-09-01?AWSAccessKeyId=AKxxxxxxxxxx

&Action=ListOrders

&SellerId=A10zzzzzzz

&SignatureVersion=2

&Timestamp=2018-09-17T21%3A35%3A57Z

&Version=2013-09-01

&Signature=3GigipfRsQgzzzzzzzzzzzzzs%3D

&SignatureMethod=HmacSHA256

Above HTTP POST can be used as below for your Body in ZappySys Driver. Rest of the parameters are automatically supplied by system. See special placeholder named [$tag$] this gets replaced at runtime when you set EnablePageTokenForBody=True and HasDifferentNextPageInfo=True

1	Action=ListOrders[$tag$]&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z

Filter

Change this parameter according to XML structure in the response.
For example: If you see below response (first xml) in Scratchpad then your Filter will be
$.ListOrdersResponse.ListOrdersResult.Orders.Order[*]
to enable pagination refer to Sample listed here (click Example response at the bottom of that page). In second response and onwards your Filter should be below (See Bold Part).
$ListOrdersByNextTokenResponse.ListOrdersByNextTokenResult.Orders.Order[*]However in Driver we replace ByNextToken with [$tag$]. Like below. Using [$tag$] will automatically pick up the correct filter based on page number. You must set HasDifferentNextPageInfo=True to use [$tag$] feature.$.ListOrders[$tag$]Response.ListOrders[$tag$]Result.Orders.Order[*]Example of first page response

<?xml version="1.0"?>

<ListOrdersResponse xmlns="https://mws.amazonservices.com/

Orders/2013-09-01">

<NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=</NextToken>

</Orders>

</ListOrdersResult>

</ListOrdersResponse>

Example of next page response

<?xml version="1.0"?>

<ListOrdersByNextTokenResponse xmlns="https://mws.amazonservices.com/

Orders/2013-09-01">

<NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=</NextToken>

</Orders>

</ListOrdersResult>

</ListOrdersResponse>

NextUrlAttribute

This property defines which attributes indicate Token for next page. You can use this token in Body of next request (see NextUrlSuffix and EnablePageTokenForBody). You must set EnablePageTokenForBody=true to use Extracted token in Body.

StopIndicatorAttribute

Some APIs like GetReportRequestList stop pagination based on HasNext attribute value. (ListOrders doesnt need this property to be set). Use this property to extract HasNext from response by supplying correct Filter expression. You also need to set NextUrlEndIndicator property which defines static value which indicates the last page. Example properties as below.

1 2	NextUrlEndIndicator=false StopIndicatorAttributeOrExpr= $.GetReportRequestList[$tag$]Response.GetReportRequestList[$tag$]Result.HasNext

NextUrlSuffix

This is used to create a string for NextToken attribute for next page request. If you want to just append extracted token to Body then you dont have to set this but in our case, we have to use &NextToken=EncodedValueOfExtractedToken so we have used &NextToken=<%nextlink_encoded%> expression.

PagePlaceholders

This property contains [$tag$] values for first page and next pages. You can define tags for filter, body or header. Each pair must be pipe delimited. First value or pair is [$tag$] for first request and second value of the pair is [$tag$] for any next request afterwards. Our first request use blank value for [$tag$].
Example: PagePlaceholders=’body=|ByNextToken;filter=|ByNextToken’

HasDifferentNextPageInfo

Set to True – This enables use of [$tag$] inside body, filter, headers to use different values of first request and second onwards requests.

EnablePageTokenForBody

Set to True – This appends value extracted from NextUrlSuffix inside body (e.g. NextToken).

Amazon MWS API Pagination (For Old Version)

So in previous section we saw how to achieve pagination in one step (for newer version of SSIS PowerPack). But there will be a case you cannot use latest SSIS PowerPack or you want to take complete control on each aspect of pagination. In such case use below method (Depreciated). In newer version we added many new properties so you dont have to do Loop pattern like below.

Sample SSIS Package

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

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

Download and Install SSIS PowerPack
Create test package
From SSIS toolbox drag Data Flow task and double click task to go to Data Flow designer
Drag ZS XML Source from SSIS Toolbox
Configure XML Source as below
SSIS XML Source – Read data from Amazon MWS API (Amazon Marketplace Web service call)
Drag OLEDB destination from SSIS Toolbox.
Connect XML Source to OLEDB Destination and map input column to target table
Execute dataflow
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)

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)

Calling Amazon Product Advertising API

If you have need to call Amazon Product Advertising API then also you can use above techniques (Use MWS Provider on OAuth connection) to call Product Advertise API. Both API (MWS API and Product Advertise API) use same signature hashing algorithm so its possible to use MWS Provider for Hashing. You may have to change your API URL parameters as needed when you call Amazon Product Advertising API. Basically when you call any MWS or Product Advertising API using GET method then OAuth connection manager automatically appends Signature to URL at runtime.

Import data from MWS Custom Reports (Inventory Report Example)

Now lets look at how to extract data from Custom Report API . Reading data from Custom Reports not single step process because report generation is Job style API. Which means you send report request and wait until its done. Once Report ready you have to read in CSV format or XML. Some Reports only available in CSV format. Check this post to understand how complex it can be to get data. We will make it simple for you to understand this in 3 steps. Basically calling reports requires minimum 3 API calls (see below)

Create a new report request – Cal RequestReport API . It returns ReportRequestId (You can use it in the next step)
Check Report Status see its done – Call GetReportRequestList API (Pass ReportRequestId got in the previous step to get data for only one Report request we care). Keep checking Report Status every few seconds until you get _DONE_ status in response.
Read Report Data – Call GetReport. This API call returns CSV or XML data. So use correct component for this step. If CSV data is returned then we must use CSV Source rather than XML Driver.

Now lets see how to read Amazon MWS custom report in SSIS. In this example, We will get Inventory by calling _GET_MERCHANT_LISTINGS_DATA_ report type.

Download Sample Package for MWS Report

To make things simple we have created a demo package – ready to go.
Download SSIS 2012 Demo – Amazon_MWS_API_GetReport_2012.

Prepare demo package for first run

Once you download and extract above sample package. Import to your existing SSIS Solution. Its SSIS 2012 format so it may upgrade to higher version if you using SSIS 2014, 2016 or 2017.

Once package is opened perform following steps in before you can execute the package.

Enter MarketplaceId, SellerId in Variables. Double click OAuth Connection and enter your MWS Account Key and Secret.
Create c:\temp directory (This is where we will save final report file)
Run this package
Go to Execution log and Find ReportId (This you can hardcode in Variables for Design time Preview / Metadata changes etc)

See below package screenshot

Read data from Amazon MWS Report in SSIS (Get Inventory Listing Example)

Steps Explained

Now lets see each steps in depth. We broken whole process in 3 groups.

Create Report Request (Step1)

This is the first step how you initiate report request. Use REST API Task to call this step. Here is raw request. Many items in body added at runtime (e.g. Signature, Timestamp).

On Request tab see how we used SSIS Variables to get many values at runtime dynamically.
On Response Tab we have to extract RequestId and save into variable.

POST

https://mws.amazonservices.com/Reports/2009-01-01

AWSAccessKeyId=Axxxxxxxxx&Action=RequestReport&MarketplaceId=ATVPDKIKX0DER&ReportType=_GET_MERCHANT_LISTINGS_DATA_&SellerId=Axxxxxxxxx&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=2019-06-05T21%3A23%3A31.090Z&Signature=2AbGQQ4xxxxxxxxxxxxxxxxxxxxjJzox%2FU%3D

RequestReport Action – Generate Amazon MWS Custom Report

Extract value from XML response using XPATH and save to Variable (Get RequestId Example)

Here is how to configure above task

Wait until data is ready (Step2)

Once we send RepotRequest and we have RequestId we can call step2 using REST API Task. In this Task we can use Status Check Feature. This is pretty handy feature because we dont have to implement Loop in SSIS. Basically we have to keep checking report until we get _DONE_ status in the response (ReportProcessingStatus node in XML)

Here is how to configure the task.

Request Tab Configuration.
URL: https://mws.amazonservices.com/Reports/2009-01-01 Method: POST
Body: Action=GetReportRequestList&ReportRequestIdList.Id.1={{User::ReportRequestId}}&MarketplaceId={{User::MarketplaceId}}&SellerId={{User::SellerId}}
Response Setting Tab Configuration
Response content type: Xml
Filter Expression: //*[local-name() = 'ReportProcessingStatus']
Status Check Tab
Check Enable Status
Success Value: _DONE_ Check Every 5 Seconds

REST API Task – Status Check Feature (Wait until data is ready loop)

GetReportId (Step3)

Once above task finished we can call same request again but this time we have to configure slightly different way.

Here is how to configure the task.

Request Tab Configuration.
URL: https://mws.amazonservices.com/Reports/2009-01-01 Method: POST
Body: Action=GetReportRequestList&ReportRequestIdList.Id.1={{User::ReportRequestId}}&MarketplaceId={{User::MarketplaceId}}&SellerId={{User::SellerId}}
Response Setting Tab Configuration
Response content type: Xml
Filter Expression: //*[local-name() = 'GeneratedReportId'] Check Save to Variable: Save to User::ReportId
Status Check Tab
Make Enable Status is unchecked

Get Report Data (Step4,Step5 or Step6)

Now we are ready to ready our report. There are two ways we can do in SSIS.

First approach (step 4,5) is download the report and save to local disk and then in the next step we can use data flow (CSV Source) to parse the saved file.
Second approach (step6) is rather than saving to disk we can directly call API using CSV File source and parse content that way. Lets talk each approach briefly.

Step4,5 – Load Report From File

If you wish to save Report on disk then you can use REST API Task and call request like below.

Here is how to configure the task to download the file.

Request Tab Configuration.
URL: https://mws.amazonservices.com/Reports/2009-01-01 Method: POST
Body: Action=GetReport&ReportId={{User::ReportId}}&MarketplaceId={{User::MarketplaceId}}&SellerId={{User::SellerId}}
Response Setting Tab Configuration
Response content type: Xml
Filter Expression: //*[local-name() = 'GeneratedReportId'] Check Save to Variable: Save to User::ReportId
On Response Settings Tab : Set Response charset to Western European [Windows 1252] (or directly set via Properties Grid to Windows-1252 under ResponseCharset Property)
Status Check Tab
Make Enable Status is unchecked

To read download CSV file use settings described in below section except URL change it with local file path.

Step6 – Load report from URL

If you wish to load directly from URL then use same setting as above but in CSV Source inside data flow.

Here is how to configure CSV Source.

Request Tab Configuration.
URL: https://mws.amazonservices.com/Reports/2009-01-01 Method: POST
Body: Action=GetReport&ReportId={{User::ReportId}}&MarketplaceId={{User::MarketplaceId}}&SellerId={{User::SellerId}}
General Tab
Column Delimiter: {TAB}
On Encoding Tab : Set to Western European [Windows 1252] (or directly set via Properties Grid to Windows-1252 under CharacterSet Property)

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).