Video tutorial

Watch this quick video to see the integration in action. It walks you through the end-to-end setup, including:

• Installing the SSIS PowerPack
• Configuring a secure connection to Amazon Selling Partner (SP-API)
• Working with Amazon Selling Partner (SP-API) data directly inside SSIS
• Exploring advanced API Source features

Once you are done watching, simply follow the step-by-step written guide below to configure your data source.

Prerequisites

Before we begin, make sure the following prerequisites are met:

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

Make generic REST API request in SSIS

1. Open Visual Studio and click Create a new project.

2. Select Integration Services Project. Enter a name and location for your project, then click OK.

3. From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:

   

4. Make sure you are in the Data Flow Task designer:

   

5. Read the data from the source, using any desired source component. You can even make an API call using the ZappySys JSON/XML/API Source and read data from there. In this example, we will use an OLE DB Source component to read real-time data from a SQL Server database.

6. From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
   

7. Select New Connection to create a new connection:
   

   API Destination - Amazon Selling Partner (SP-API)

   Read and write Amazon Selling Partner (SP-API) data effortlessly. Integrate, manage, and automate listings, orders, payments, and reports — almost no coding required.

   

8. To configure the Amazon Selling Partner (SP-API) connector, choose one of the following methods:

   • Choose from Popular Connector List: Select a pre-installed service directly from the dropdown menu.
   • Search Online: Use this to find and download a new connector file to your computer.
   • Use Saved/Downloaded File: Once the file is downloaded, browse your local drive to load it into the configuration.

   After that, just click Continue >>:

   Amazon Selling Partner (SP-API)

   

9. Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

   Private app

   Amazon Selling Partner (SP-API) authentication

   Amazon Sellers can use SP-API to set up private integrations and build solutions exclusively for their Amazon store. Private app is available only to your organization and is self-authorized. A private developer builds application(s) that integrate their own company with Amazon APIs. [API reference]

   Perform the following steps to authenticate calls using Amazon SP-API Private app:

   1. Register as an Amazon Private SP-API Developer. You may need to wait for a day or two to get approved (check approval status).
   2. Once your developer account is approved, login to your account, visit developer console and click Add new app client button to create a Private app:
   3. Continue by naming your application in the App name field, choose SP API as API Type, and select the Roles for your app (i.e. permissions):
   4. Once you do that, click View link in LWA credentials column to copy Client identifier and Client secret (we will use them later):
   5. Now it's time to Authorize your app:
   6. Finish authorizing it by presing Authorize app button:
   7. Finally, copy the Refresh Token (we will use it in the next step):
   8. Now go to SSIS package or ODBC data source and use Private app authentication configuration:
      • In the ClientId field paste the Client identifier value you copied in the previous step.
      • In the ClientSecret field paste the Client secret value you copied in the previous step.
      • Leave the default value in the TokenUrl field.
      • In the Refresh Token field paste the Refresh Token value you copied in the previous step.
   9. Click Test Connection to confirm the connection is working.
   10. Done! Now you are ready to use Amazon Selling Partner (SP-API) Connector!

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to Private app [OAuth]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Press Generate Token button to generate the tokens.
   5. Finally, hit OK button:

   Amazon Selling Partner (SP-API)

   Private app [OAuth]

   https://sellingpartnerapi-na.amazon.com

   Required Parameters
   ClientId	Fill-in the parameter...
   ClientSecret	Fill-in the parameter...
   TokenUrl	Fill-in the parameter...
   Optional Parameters
   TokenUIMode	OnlyRefreshToken
   AuthUrl (Do not Use for Private app - Self Authorization)	https://sellercentral.amazon.com/apps/authorize/consent?application_id=[YOUR-APPLICATION-ID]&version=beta
   OrdersApiVersion	v0
   SellerApiVersion	v1
   ShippingApiVersion	v1
   ServicesApiVersion	v1
   FbaApiVersion	v1
   SalesApiVersion	v1
   ReportsApiVersion	2021-06-30
   ProductsFeesApiVersion	v0
   ProductPricingApiVersion	v0
   CatalogItemsApiVersion	2022-04-01
   VendorOrdersApiVersion	v1
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

   Find full details in the Amazon Selling Partner (SP-API) Connector authentication reference.

10. Select Generic Table (Bulk Read / Write) table from the dropdown, then select Insert, Update as operation, and hit Preview Data:

    

    API Destination - Amazon Selling Partner (SP-API)

    Read and write Amazon Selling Partner (SP-API) data effortlessly. Integrate, manage, and automate listings, orders, payments, and reports — almost no coding required.

    Amazon Selling Partner (SP-API)

    Generic Table (Bulk Read / Write)

    Insert, Update

    Required Parameters
    Url	Fill-in the parameter...
    Request Method	Fill-in the parameter...
    Optional Parameters
    IsMultiPart
    Filter
    Request Format (Content-Type)	Default
    Body	{$rows$}
    JsonOutputFormat	Multicontent
    DoNotOutputNullProperty
    Batch Size (Default=1)	1
    Meta Detection Order	StaticDynamicVirtual
    Input Columns - For Mapping (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
    Output Columns (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
    Request Format
    Response Format	Default
    Headers	Accept: */* || Cache-Control: no-cache
    Csv - Column Delimiter	,
    Csv - Row Delimiter	{NEWLINE}
    Csv - Quote Around Value	True
    Csv - Always Quote regardless type
    Encoding
    CharacterSet
    Writer DateTime Format
    Csv - Has Header Row	True
    Xml - ElementsToTreatAsArray
    Layout Map	<?xml version="1.0" encoding="utf-8"?> <!-- Example#1: Output all columns --> <settings> <dataset id="root" main="True" readfrominput="True" /> <map src="*" /> </settings> <!-- Example#2: Records under array <?xml version="1.0" encoding="utf-8"?> <settings singledataset="True"> <dataset id="root" main="True" readfrominput="True" /> <map name="MyArray" dataset="root" maptype="DocArray"> <map src="OrderID" name="OrderID" /> <map src="OrderDate" name="OrderDate" /> </map> </settings> --> <!-- Example#3: Records under nested section <?xml version="1.0" encoding="utf-8"?> <settings> <dataset id="dsRoot" main="True" readfrominput="True" /> <map name="NestedSection"> <map src="OrderID" name="OrderID_MyLabel" /> <map src="OrderDate" name="OrderDate_MyLabel" /> </map> </settings> -->

    

11. Finally, map the desired columns:

    API Destination - Amazon Selling Partner (SP-API)

    Read and write Amazon Selling Partner (SP-API) data effortlessly. Integrate, manage, and automate listings, orders, payments, and reports — almost no coding required.

    

12. That's it; we successfully configured the POST API Call. In a few clicks we configured the Amazon Selling Partner (SP-API) API call using ZappySys Amazon Selling Partner (SP-API) Connector

    

Conclusion

And there you have it — a complete guide on how to make generic REST API request (bulk write) in SSIS without writing complex code. All of this was powered by Amazon Selling Partner (SP-API) Connector, which handled the REST API pagination and authentication for us automatically.

Download the trial now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

More actions supported by Amazon Selling Partner (SP-API) Connector

Got another use case in mind? We've documented the exact setups for a variety of essential Amazon Selling Partner (SP-API) operations directly in SSIS, so you can skip the trial and error. Find your next step-by-step guide below:

• Download Report File to Local Disk
• Get FBA Inventory Summaries (Fulfilled By Amazon)
• Get Market Place Participations
• Get Order (Single)
• Get Order Items (For Single Order)
• Get Orders
• Get Orders Items (For All Orders **Slow**)
• Get Report Data - Comma Separated File (CSV)
• Get Report Data - JSON File
• Get Report Data - Tab Separated File (TSV)
• Get Report Data - XML File
• Get Report Filters
• Get Report Options
• Get Report Types
• Get Vendor PurchaseOrder Items
• Get Vendor PurchaseOrders
• Search Catalog Items
• Make Generic REST API Request

More Amazon Selling Partner (SP-API) integrations