In this post we will see how to access your Seller Central Data via Amazon SP-API in SSIS API Sorce/destination or ODBC API Driver / 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 API Driver
- Download ODBC driver from here (click Download button).
- 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
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
- Click view under LWA Credentials
- 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.
- Go to Developer Central App Page
and click Authorize - 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 Amazon SP-API using API Driver
After you install ZappySys ODBC Drivers, you can use Driver functionality a 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 API ODBC Driver.
- 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.
- From the driver list, select ZappySys API Driver, choose Amazon Selling Partner (SP-API) from the connector list, and press Continue
Configure SP-API API Driver
Copy your Client ID, Client Secret, and Refresh Token (Obtained in the previous section) and paste them on the Connector. Press Test connection and then OK to save the configuration
Test Amazon SP-API API Driver request
Now go to the preview tab and click the 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)
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 about how to use our API source/destination 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