How to call Amazon SP-API (Selling Partner) in SSIS / ODBC Apps (e.g. PowerBI)

[nayan]

In this post we will see how to access your Seller Central Data via Amazon SP-API in SSIS or other ODBC / Java Apps. Amazon SP-API (Selling Partner API) can be used to access your Amazon Seller Central data if you are selling on Amazon. Calling SP-API was a lot harder until Amazon announced a change in Oct 2023.

Connect Amazon SP-API in ODBC Apps

If you have a need to access Amazon SP-API in ETL/ BI / Reporting Apps like Power BI, Crystal Reports, Excel, Tableau … or Programming language like Python, Java, C# no worries. We will show you how to use ODBC Driver to achieve this.

Download JSON Driver

1. Download ODBC driver from here (click Download button).
2. Install ODBC PowerPack

App Registration and Obtain Credentials

Video Tutorial - Register App for SP-API

We suggest you check this video to see how to Register an App and get Client Id and Secret to call SP-API

How to make SP-API requests in 2024 - Big Update from Amazon

Obtain Client Id and Client Secret (LWA credentials)

Previous video showed how to get LWA Credentials which we is nothing but OAuth ClientID and Client Secret (some Apps call it App Id and App Secret)

Here is where you can get it

1. Click view under LWA Credentials
   
2. on popup UI copy Client identifier (its Client Id for OAuth UI in next section), and then expand Client secret and copy
   
   

Obtain Refresh Token (Authorize SP-API App)

Next step is create a refresh token which we will need in the next section.

1. Go to Developer Central App Page
   
   
   and click Authorize
2. On the Add Authorizations page click Authorize App like below and click Copy to get RefreshToken. Save this token. We will enter in the next section on OAuth connection → Refresh Token field
   
   

Configure ODBC DSN for SP-API using JSON Driver

After you install ZappySys ODBC Drivers you can use Driver functionality few ways (see below).

In this section you will learn how to Configure ODBC DSN. Later on this DSN can be used to use ZappySys Driver. ODBC DSN can be stored at Machine Level (for all users) or Current User Level. In this case we are going to load data into Microsoft Excel from JSON(In this case its from Zip) file using ZappySys JSON ODBC Driver.

1. Type odbcad32.exe in your search box and launch the DSN Config utility.
   * If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
   
2. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys JSON Driver].
   

Configure SP-API OAuth Connection

Now its time to setup few properties on our Driver to call SP-API. For that double click the DSN you created in the previous section.

If you have connection string you can load directly on the UI using this way.

Here is a sample connectionstring you can load to test.
NOTE: Change AuthUrl, TokenUrl, ClientId, ClientSecret, RefreshToken, DataPath, Filter etc as per your need before you load it on the UI.

DRIVER={ZappySys JSON Driver};
DataPath='https://sellingpartnerapi-na.amazon.com/sellers/v1/marketplaceParticipations';
DataConnectionType='OAuth';
AuthUrl='https://sellercentral.amazon.com/apps/authorize/consent?application_id=amzn1.application-oa2-client.145c7xxxxxxxxxx&version=beta';
TokenUrl='https://api.amazon.com/auth/o2/token';
ScopeSeparator='{space}';
ClientId='amzn1.application-oa2-client.145c7xxxxxxxx';
ClientSecret='amzn1.oa2-cs.v1.e347203effc0899b12c6cff2311e4b8b357811310837e34dc49d83eb93f92bb6';
UseCustomApp=1;
CustomAuthHeader='x-amz-access-token';
AccessToken='Xyzxxxxx';
RefreshToken='Abcdxxxx';
Filter='$.payload[*]';
RequestMethod='GET';
RequestHeaders='cache-control: no-cache || Accept: */*'

1. Open ODBC DSN and enter URL for SP-API you like to call. For example if I like to pull market list I can enter below URL (its very simple one so no parameters needed). But you can call any valid API (GET or POST), same way you enter in Postman.
   https://sellingpartnerapi-na.amazon.com/sellers/v1/marketplaceParticipations
   
2. Choose Connection Type as OAuth and Click to Configure
3. On OAuth enter following settings
   Client ID and Client Secret (Obtained in the previous section)
   Token URL: https://api.amazon.com/auth/o2/token
   Refresh Token: Obtained in the previous section (see Authorize Action)
   NOTE: For Private App, you do not need to worry about the Authorization URL. You only need a Token URL (see this help for self-authorization)
   
   
4. Now go to Advanced Tab. Enter following value in Custom Auth Header
   x-amz-access-token
5. Click ok

Test Amazon SP-API request

Now go to preview tab and click Table dropdown and select the table you wish to access. This table is from URL you entered on General Tab

Accessing Amazon SP-API ODBC DSN in Apps (e.g. Power BI)

Now once DSN is ready we can access that in any ODBC compliant apps. For example to Access SP-API data in Power BI you can follow these steps

Click on this link to see other apps

Connect Amazon SP-API in SSIS

You can follow this page to learn more how to access JSON data in SSIS

Conclusion

By following these steps, you can seamlessly connect to Amazon SP-API in Apps like Power BI (ODBC Apps) or SSIS, utilizing the ZappySys tool for efficient data retrieval and integration. If you encounter any issues or need further assistance, you can contact our support team via chat on our website or through email at support@zappysys.com.