{"id":822,"date":"2016-10-15T04:42:21","date_gmt":"2016-10-15T04:42:21","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=822"},"modified":"2026-03-11T07:08:08","modified_gmt":"2026-03-11T07:08:08","slug":"call-amazon-mws-api-using-ssis-marketplace-web-service","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-amazon-mws-api-using-ssis-marketplace-web-service\/","title":{"rendered":"How to call Amazon MWS API using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n
\n\u26a0\ufe0f<\/span> Deprecation Notice: MWS API is deprecated<\/strong>
\nAmazon’s MWS (Marketplace Web Service) is being deprecated and replaced by the newer AWS Selling Partner API (SP-API).<\/strong> For a more robust and secure integration, we recommend<\/strong> using our AWS Selling Partner (SP-API) Connector<\/a>.<\/strong> As Amazon is phasing out MWS functionality and eventually plans to fully deprecate it.
\n<\/div><\/div>\n

\"\"<\/a>In this post you will learn how to call Amazon MWS API<\/em> (Amazon Marketplace Web Service<\/a>) or Amazon Product Advertising API<\/a> using SSIS PowerPack<\/a>.<\/p>\n

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

Prerequisites<\/h2>\n

This post assumes following things<\/p>\n

    \n
  1. Basic knowledge of SSIS and XML format.<\/li>\n
  2. Amazon MWS Account and and valid AWSAccessKeyId and Secret Key to call Amazon MWS API (How to obtain Key \/ Secret and SellerId ? )<\/a>.<\/li>\n
  3. You have tested few API calls using Amazon MWS Scratchpad<\/a>\u00a0this will give you idea about various API you can call and parameters you need to pass.<\/li>\n<\/ol>\n

    Testing MWS Requests using ScratchPad<\/h2>\n

    Very first step we recommend before you call MWS API in SSIS is to get familiar with\u00a0Amazon MWS Scratchpad<\/a>\u00a0tool. Check this article for detailed steps<\/a>.\u00a0 You can also use Fiddler<\/a> to debug some requests.<\/p>\n

    \"Calling<\/a>

    Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) – ListMatchingProducts Example<\/p><\/div>\n

    Call Amazon MWS API using SSIS<\/h2>\n

    To consume data from any XML Source (File, SOAP Web Service or XML API) you can use \u00a0ZappySys XML Source
    \nconnector.<\/a>\u00a0or     REST API Task.<\/a>\u00a0In below example you will see how to call Amazon MWS API and save output into Variable.\u00a0With this approach you can call any API\u00a0(GET \/ POST \/ DELETE \/ PUT)<\/p>\n

      \n
    1. Download and Install SSIS PowerPack<\/li>\n
    2. Create test package<\/li>\n
    3. From SSIS toolbox drag ZS REST API Task<\/a>\u00a0 and double click on the task to configure it<\/li>\n
    4. Select Request URL Access Mode = [Url from Connection]<\/li>\n
    5. Enter URL as below\n
      https:\/\/mws.amazonservices.com\/Orders\/2013-09-01<\/pre>\n
      In above URL
      \n** mws.amazonservices.com => This is your endpoint. If you are not in USA then       Click here<\/a> to find correct endpoint
      \n** \/Orders => That’s your API you want to call
      \n** \/2013-09-01 => That’s your API version (You have to add this same version in your POST Body too (see next section).<\/li>\n
    6. In Select Url Connection dropdown select New OAUTH connection option.<\/li>\n
    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.\n
    8. Another Setting you may turn on is Retry Handling. This allows to over come ThresholdLimitReached error when we call API too fast<\/a>.\n
    9. Now select Method=POST, and in the body enter following\n
      Action=GetServiceStatus&SellerId=A77XXXXXXX&Version=2013-09-01<\/pre>\n
      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<\/p>\n
      Action=YourActionNameWithNextToken&SellerId=A77XXXXXXX&Version=2013-09-01&NextToken={{User::vNextToken,FUN_URLENC}}<\/pre>\n
      NOTE:<\/strong> 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<\/strong> format specifier, e.g.\u00a0{{User::myVariable,FUN_URLENC}}<\/strong>. If you use a hard-coded value, and not sure if it is URL-encoded, then encode it with the same FUN_URLENC<\/strong> format specifier\/function, e.g. <<myValue,FUN_URLENC>><\/strong>. Always check if SSIS configuration matches the one you see in\u00a0MWS ScratchPad Request<\/em> Details<\/em>.
      \nFormat:<\/strong>
      \nparam1=value1&param2=<<value2,FUN_URLENC>>&param3={{User::myVar,FUN_URLENC}}<\/code><\/div><\/div><\/li>\n
    10. Now click Test and see content in the Preview window.\n
    11. If you wish to save response data into SSIS variable then goto response Tab and check save option and select variable name.<\/li>\n<\/ol>\n
      Amazon MWS API Pagination<\/h2>\n
      Many Amazon MWS API calls by default don’t return all records (Explained here<\/a>). Rather than returning all rows you may get partial response (e.g. Max 50\u00a0or 100 rows). If you want to\u00a0get all records then you\u00a0have to\u00a0use NextToken\u00a0\u00a0found 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.<\/p>\n
      We also documented similar use case of Amazon MWS Pagination for Power BI (Check this one)<\/a> its mostly same setup except 2-3 properties might be named different way.<\/p>\n
      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<\/a> (First Request) and ListOrderByNextToken<\/a> (Second+ requests)<\/p>\n
      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<\/a> for each API Endpoint.<\/div><\/div>\n
      First Request<\/strong><\/p>\n
      POST https:\/\/mws.amazonservices.com\/Orders\/2013-09-01\r\n\r\nAction=ListOrders&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z<\/pre>\n
      First Response<\/strong><\/p>\n
      <?xml version=\"1.0\"?>\r\n<ListOrdersResponse xmlns=\"https:\/\/mws.amazonservices.com\/\r\n    Orders\/2013-09-01\">\r\n    <ListOrdersResult>\r\n        <NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=<\/NextToken>\r\n        <LastUpdatedBefore>2017-02-25T18%3A10%3A21.687Z<\/LastUpdatedBefore>\r\n        <Orders>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n        <\/Orders>\r\n    <\/ListOrdersResult>\r\n<\/ListOrdersResponse><\/pre>\n
       <\/p>\n
      Second Request or higher (MWS API with Pagination using NextToken)<\/strong><\/p>\n
      POST https:\/\/mws.amazonservices.com\/Orders\/2013-09-01\r\n\r\nAction=ListOrdersByNextToken&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z&NextToken=YOUR_ENCODED_TOKEN_FROM_PREV_REQUEST<\/pre>\n
      Second or higher Response<\/strong><\/p>\n
      <?xml version=\"1.0\"?>\r\n<ListOrdersByNextTokenResponse xmlns=\"https:\/\/mws.amazonservices.com\/\r\n    Orders\/2013-09-01\">\r\n    <ListOrdersByNextTokenResult>\r\n        <NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=<\/NextToken>\r\n        <LastUpdatedBefore>2017-02-25T18%3A10%3A21.687Z<\/LastUpdatedBefore>\r\n        <Orders>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n        <\/Orders>\r\n    <\/ListOrdersResult>\r\n<\/ListOrdersResponse><\/pre>\n
      Sample SSIS Package<\/h3>\n
      If you want to see complete working package for pagination pattern then download from here<\/a>. Change AccessKey, SecretKey, SellerID and Path as needed to make this package work.<\/p>\n
      Here is the\u00a0screenshot of example SSIS package for Amazon MWS.<\/p>\n
      \"Amazon<\/a>
      Amazon MWS APi Pagination (ListOrders , ListOrdersByNextToken Example)<\/p><\/div>\n
       <\/p>\n
      Copy\/Paste Properties<\/h3>\n
      Here is copy\/paste values for properties listed above.<\/p>\n
      DirectPath:  https:\/\/mws.amazonservices.com\/Orders\/2013-09-01\r\nEnablePageTokenForBody: True\r\nFilter:  $.ListOrders[$tag$]Response.ListOrders[$tag$]Result.Orders.Order[*]\r\nHasDifferentNextPageInfo: True\r\nHttpRequestData:  Action=ListOrders[$tag$]&SellerId=A1xxxxxxxx&CreatedAfter=2018-09-01T04%3A00%3A00Z&MarketplaceId.Id.1=ATVPDKIKX0DER\r\nHttpRequestMethod:  POST\r\nNextUrlAttribute: $.ListOrders[$tag$]Response.ListOrders[$tag$]Result.NextToken\r\nNextUrlSuffix: &NextToken=<%nextlink_encoded%>\r\nNetUrlWait: 10000 (ms) -- slow down to avoid throttling ... Max 6 requests per minute, increase if you get API Limit Error\r\nPagePlaceholders:  body=|ByNextToken;filter=|ByNextToken\r\n<\/pre>\n
       <\/p>\n
      Understanding Pagination Properties<\/h3>\n
      Here are various properties you have to set for Pagination.<\/p>\n
      \n\n\n\n\n\n\n\n\n\n\n\n\n
      Property<\/td>\nDescription<\/td>\n<\/tr>\n
      Src<\/td>\nChange this to correct URL. Look at Scratchpad Response Details Tab and Findout first line after POST and before “?” . e.g.\u00a0\/Orders\/2013-09-01\u00a0 this will be used in your URL.
      \nExample:\u00a0<\/strong>
      \nhttps:\/\/mws.amazonservices.com\/Orders\/2013-09-01<\/td>\n<\/tr>\n
      Body (i.e. RequestData)<\/td>\nScratchpad shows HTTP POST section. You can copy that and remove\u00a0 System supplied parameters and then arrange all in one line to form your Body for request.<\/p>\n
      Example:<\/strong><\/p>\n
      POST \/Orders\/2013-09-01?AWSAccessKeyId=AKxxxxxxxxxx\r\n  &Action=ListOrders\r\n  &SellerId=A10zzzzzzz\r\n  &SignatureVersion=2\r\n  &Timestamp=2018-09-17T21%3A35%3A57Z\r\n  &Version=2013-09-01\r\n  &Signature=3GigipfRsQgzzzzzzzzzzzzzs%3D\r\n  &SignatureMethod=HmacSHA256<\/pre>\n
      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\u00a0EnablePageTokenForBody=True<\/strong> and HasDifferentNextPageInfo=True<\/strong><\/p>\n
      Action=ListOrders[$tag$]&SellerId=A10zzzzzzz&CreatedAfter=2018-09-01T04%3A00%3A00Z<\/pre>\n<\/td>\n<\/tr>\n
      Filter<\/td>\nChange this parameter according to XML structure in the response.
      \nFor example: If you see below response (first xml) in Scratchpad then your Filter will be
      \n$.ListOrdersResponse.ListOrdersResult.Orders.Order[*]<\/strong>
      \nto enable pagination refer to Sample listed here<\/a> (click Example response at the bottom of that page). In second response and onwards your Filter should be below (See Bold Part).
      \n$ListOrdersByNextToken<\/strong>Response.ListOrdersByNextToken<\/strong>Result.Orders.Order[*]However in Driver we replace ByNextToken<\/strong> with [$tag$]<\/strong>. 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[*]<\/strong>Example of first page response<\/strong><\/p>\n
      <?xml version=\"1.0\"?>\r\n<ListOrdersResponse xmlns=\"https:\/\/mws.amazonservices.com\/\r\n    Orders\/2013-09-01\">\r\n    <ListOrdersResult>\r\n        <NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=<\/NextToken>\r\n        <LastUpdatedBefore>2017-02-25T18%3A10%3A21.687Z<\/LastUpdatedBefore>\r\n        <Orders>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n        <\/Orders>\r\n    <\/ListOrdersResult>\r\n<\/ListOrdersResponse><\/pre>\n
      Example of next page response<\/strong><\/p>\n
      <?xml version=\"1.0\"?>\r\n<ListOrdersByNextTokenResponse xmlns=\"https:\/\/mws.amazonservices.com\/\r\n    Orders\/2013-09-01\">\r\n    <ListOrdersByNextTokenResult>\r\n        <NextToken>2YgYW55IxxxxxxxxxxxxVyZS4=<\/NextToken>\r\n        <LastUpdatedBefore>2017-02-25T18%3A10%3A21.687Z<\/LastUpdatedBefore>\r\n        <Orders>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n            <Order> ....... <\/Order>\r\n        <\/Orders>\r\n    <\/ListOrdersResult>\r\n<\/ListOrdersResponse><\/pre>\n<\/td>\n<\/tr>\n
      NextUrlAttribute<\/td>\nThis property defines which attributes indicate Token for next page. You can use this token in Body of next request (see\u00a0NextUrlSuffix and\u00a0EnablePageTokenForBody). You must set\u00a0EnablePageTokenForBody=true<\/strong> to use Extracted token in Body.<\/td>\n<\/tr>\n
      StopIndicatorAttribute<\/td>\nSome APIs like\u00a0 GetReportRequestList<\/a>\u00a0stop pagination based on HasNext<\/strong> 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\u00a0NextUrlEndIndicator property which defines static value which indicates the last page. Example properties as below.<\/p>\n
      NextUrlEndIndicator=false\r\nStopIndicatorAttributeOrExpr= $.GetReportRequestList[$tag$]Response.GetReportRequestList[$tag$]Result.HasNext<\/pre>\n<\/td>\n<\/tr>\n
      NextUrlSuffix<\/td>\nThis 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\u00a0&NextToken=<%nextlink_encoded%><\/strong> expression.<\/td>\n<\/tr>\n
      PagePlaceholders<\/td>\nThis 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$]\u00a0 for first request and second value of the pair is [$tag$] for any next request afterwards. Our first request use blank value for [$tag$].
      \nExample:\u00a0\u00a0PagePlaceholders=’body=|ByNextToken;filter=|ByNextToken’<\/strong><\/td>\n<\/tr>\n
      HasDifferentNextPageInfo<\/td>\nSet to True – This enables use of [$tag$] inside body, filter, headers to use different values of first request and second onwards requests.<\/td>\n<\/tr>\n
      EnablePageTokenForBody<\/td>\nSet to True – This appends value extracted from\u00a0NextUrlSuffix<\/strong>\u00a0inside body (e.g. NextToken).<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
      Amazon MWS API Pagination (For Old Version)<\/h2>\n
      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).\u00a0 In newer version we added many new properties so you dont have to do Loop pattern like below.<\/p>\n
      Sample SSIS Package<\/h3>\n
      If you want to see complete working package for pagination pattern then download from here<\/a>. Change AccessKey, SecretKey, SellerID and Path as needed to make this package work.<\/p>\n
      Here is the\u00a0screenshot of example SSIS package for Amazon MWS.<\/p>\n
      \"SSIS<\/a>
      SSIS Example Package – Read data from Amazon MWS Web service (API Call). Paginate API calls using NextToken<\/p><\/div>\n
      Load Amazon MWS API data into SQL Server using SSIS<\/h2>\n
      In this section we will see how to use ZappySys XML Source connector<\/a>\u00a0to read data from Amazon MWS Web Service and load into SQL Server<\/p>\n
      For making things simple we are calling<\/p>\n
        \n
      1. Download and Install SSIS PowerPack<\/li>\n
      2. Create test package<\/li>\n
      3. From SSIS toolbox drag Data Flow task and double click task to go to Data Flow designer<\/li>\n
      4. Drag ZS XML Source<\/a> from SSIS Toolbox<\/li>\n
      5. Configure XML Source as below\n
      6. Drag OLEDB destination from SSIS Toolbox.<\/li>\n
      7. Connect XML Source to OLEDB Destination and map input column to target table<\/li>\n
      8. Execute dataflow\n
        \"Get<\/a>
        Get data from Amazon MWS API – Save to File or SQL Server using SSIS<\/p><\/div><\/li>\n<\/ol>\n
        Working with Amazon MWS Feed API (e.g. Upload file)<\/h2>\n
        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.<\/p>\n
        Upload Amazon MWS Feed File in TSV \/ CSV\u00a0format (Tab separated)<\/h3>\n
        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.<\/p>\n
        \"Call<\/a>
        Call Amazon MWS Feed API – Upload CSV file format (Tab separated values – TSV)<\/p><\/div>\n
        Upload Amazon MWS Feed File in XML format<\/h3>\n
        To upload feed file in XML format use same settings as above except Content Type should be XML (text\/xml) (Select in the dropdown)<\/p>\n
        Calling Amazon Product Advertising API<\/h2>\n
        If you have need to call Amazon Product Advertising API<\/a> 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.<\/p>\n
        Import data from MWS Custom Reports (Inventory Report Example)<\/h2>\n
        Now lets look at how to extract data from Custom Report API<\/a>\u00a0. Reading data from Custom Reports not single step process because report generation is Job style API. Which means you send report request and wait\u00a0 until its done. Once Report ready you have to read in CSV format or XML. Some Reports only available in CSV format.\u00a0Check this post<\/a> 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)<\/p>\n
          \n
        1. Create a new report request – Cal\u00a0RequestReport<\/a>\u00a0API . It returns ReportRequestId<\/strong> (You can use it in the next step)<\/li>\n
        2. Check Report Status see its done – Call GetReportRequestList<\/a>\u00a0API (Pass ReportRequestId<\/strong> 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_<\/strong> status in response.<\/li>\n
        3. Read Report Data – Call GetReport<\/a>. 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<\/a> rather than XML Driver<\/a>.<\/li>\n<\/ol>\n
          Now lets see how to read Amazon MWS custom report in SSIS. In this example, We will get Inventory by calling\u00a0_GET_MERCHANT_LISTINGS_DATA_<\/strong> report type.<\/p>\n
          Download Sample Package for MWS Report<\/h3>\n
          To make things simple we have created a demo package – ready to go.
          \n          Download SSIS 2012 Demo – Amazon_MWS_API_GetReport_2012<\/a>.<\/p>\n
          Prepare demo package for first run<\/h3>\n
          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.<\/p>\n
          Once package is opened perform following steps in before you can execute the package.<\/p>\n
            \n
          1. Enter MarketplaceId, SellerId in Variables. Double click OAuth Connection and enter your MWS Account Key and Secret.<\/li>\n
          2. Create c:\\temp directory (This is where we will save final report file)<\/li>\n
          3. Run this package<\/li>\n
          4. Go to Execution log and Find ReportId (This you can hardcode in Variables for Design time Preview \/ Metadata changes etc)<\/li>\n<\/ol>\n
            See below package screenshot<\/p>\n
            \"Read<\/a>
            Read data from Amazon MWS Report in SSIS (Get Inventory Listing Example)<\/p><\/div>\n
            Steps Explained<\/h3>\n
            Now lets see each steps in depth. We broken whole process in 3 groups.<\/p>\n
            Create Report Request (Step1)<\/h4>\n
            This is the first step how you initiate report request.\u00a0 Use REST API Task<\/a> to call this step. Here is raw request. Many items in body added at runtime (e.g. Signature, Timestamp).<\/p>\n