Selling automation is a common requirement when working with Amazon Marketplace, which can help sellers grow the business exponentially. Amazon provides Amazon Marketplace Web Service (Amazon MWS) API to help Amazon sellers build reliable and scalable integrations that extend Amazon Marketplace into any business processes.
Working with Amazon MWS can be a little tricky as it requires developers to produce an Amazon Signature Version 2 which needs to be inserted in each HTTP request in order to successfully authenticate against the Amazon Marketplace API. We recognize the challenges faced by developers in working with such an API, particularly the requirement of generating the Amazon Version 2 signature in SSIS and making relevant service calls by processing XML based messages in order to facilitate efficient data integration. The purpose of this blog post is to show you how to achieve this in SSIS without writing a single line of code by leveraging the HTTP Connection Manager and XML Source component offered in the KingswaySoft SSIS Productivity Pack.
1. Register to use Amazon MWS
Before working with the Amazon MWS API, we need to register as a developer to use Amazon MWS and obtain the Amazon MWS Developer ID and Access Keys.
After the developer is registered, we need to authorize developer access to the selling accounts to get the MWS Auth Token and the Seller ID which will be used in the API authentication.
2. Establish Amazon MWS Connection
Once the developer registration is completed and the selling account is associated with the developer, we can configure the HTTP Connection Manager to establish the connection to the Amazon MWS API.
2.1 Set up Base URL
All the Amazon MWS endpoints can be found in the documentation page. In our example, we are connecting to an US account so that the Base URL in HTTP Connection Manager is set to “https://mws.amazonservices.com”.
2.2 Authentication
Setting up Amazon MWS authentication has been made extremely easy when using the HTTP Connection Manager shipped in our SSIS Productivity Pack product, as it has the built-in Amazon Signature authentication mode support.
To establish the Amazon MWS connection, simply choose “Amazon Signature” authentication mode in HTTP Connection Manager and select “Version 2” signature version, fill in the AWS Access Key and Access Secret.
3. Call Amazon MWS API
In this blog post, we will use XML Source component to call the Amazon MWS API ListOrders endpoint to fetch a list of orders created or updated during a defined time frame.
When getting orders from Amazon MWS API, the ListOrders endpoint also returns a “NextToken” value as the indicator to tell if there are more records. If “NextToken” is returned, we need to pass the “NextToken” value to ListOrdersByNextToken endpoint. If “NextToken” is not returned, there are no more orders to return. Based on the “NextToken” value, we can configure the XML Source component with the pagination strategy as the screenshot below to get all available orders:
The “Action” query string parameter is set to use an expression below to dynamically perform ListOrders action on the first request and perform ListOrdersByNextToken action on the following requests.
- (Expression Mode) Action: @[RequestCount]==0?"ListOrders":"ListOrdersByNextToken"
The “CreateAfter” parameter is configured to get orders created in last five hours using an expression like this:
- (Expression Mode) CreatedAfter: (DT_WSTR,20)(DT_DBDATE)GetUtcDate()+"T"+(DT_WSTR,20)(DT_DBTIME)DateAdd("hh" , -5, GetUtcDate() )
Request Again Rule checked the existence of “NextToken” value in the response to determine when to stop paging:
- (Expression Mode) Action: ValueExistsInXml( @[ResponseBody], "/i:ListOrdersResponse/i:ListOrdersResult/i:NextToken", "i","https://mws.amazonservices.com/Orders/2013-09-01" )
Next, we can work on the XML document design to let the XML Source component to be able to parse the API response into columns data. Simply click the “Import” button to import from XML (Web) in XML Document Designer page to import design from the actual API response.
The import from XML (Web) button will generate a document design for the ListOrders action response. The ListOrdersByNextToken action has a slightly different response than the ListOrders action, however, we can easily configure XML Source document design based on the ListOrders XML response structure to handle both ListOrders and ListOrdersByNextToken responses:
At this moment, we have finished the XML Source component configuration. The next and last step is to implement the Data Flow design to use a Union All component to combines the ListOrders order records and ListOrdersByNextToken order records into one output.
In this example, we are writing Amazon MWS orders into a SQL database table using Premium ADO NET Destination component, which is also offered in our SSIS Productivity Pack.
Sample XML Designer Settings File
We have prepared a sample XML designer settings file which can be imported to an XML Source component to help you get started with setting up the Amazon MWS integration portion of your project. The sample designer settings file can be found be downloaded here.
With the use of HTTP Connection Manager and XML Source component, working with Amazon Marketplace Web Service cannot be easier. We hope this post will give you a great sense on how to consume Amazon MWS API within SSIS.