How to call eBay REST API using SSIS

 Introduction

eBay recently launched brand new REST API with OAuth 2.0 support. This article will explain you how to call eBay REST API using SSIS to automate eBay operations (e.g. Buy/Sell) using SSIS REST API Integration Connectors / OAuth Connection Manager.

This article assumes that you have basic knowledge of SSIS (SQL Server Integration Services). We will use drag and drop approach without coding to do eBay REST API integration. We will use following custom tasks/connectors from SSIS PowerPack

SSIS PowerPack REST API Connectors can be used to consume/manage eBay data using API calls. Following connectors will be used in this article.

Custom SSIS Tasks - Call REST API Webservice (GET, POST, DELETE etc) REST API Task
SSIS OAuth Connection Manager OAuth Connection Manager
Custom SSIS Components - JSON Source (File, REST, OData) JSON Source (File, REST, OData)

Register eBay OAuth App

Before you can call any eBay REST API you have to register OAuth App. Lets look at high level steps needed to register eBay OAuth app and get credentials to make REST API Call later in this article.

  1. To call eBay REST API you will have to create eBay Developer account. Its free.
  2. Once you login to your developer portal register ebay OAuth App for Sandbox/Production. eBay supports Sandbox and Production API so we suggest you to create Apps for both API types (i.e. create key sets).

    Best practice is to test everything first using Sandbox API and then change URLs and keys to Production API when you ready to switch. To learn more about eBay API Testing in Sandbox Click here

  3. Once you register eBay OAuth App (Production or Sandbox) you will need following information for our SSIS OAuth Connection (Used later in this article)
    1. App ID (i.e. Client ID)
    2. Cert ID (i.e. Client Secret)
    3. RuName (i.e. Redirect URL). Click here to learn how to get RuName (scroll to the section which says Getting the RuName value in that link). RuName is specified in Redirect URL field on SSIS OAuth connection manager (on Advanced Tab). When you create Redirect URL you will need to specify https:// in Accept URL field as below screenshot. If you are not sure of if you don’t have server which can accept https:// redirect call then enter below URL.
      https://zappysys.com/oauth/
  4. Once OAuth App created you will need atleast one test user account to test Sandbox API (you cannot use Developer account used for App registration). You can create test user for sandbox here
  5. Once you have above steps done you are ready for your very first eBay REST API call from SSIS :)

Call eBay API using SSIS – Create new inventory item

Now lets look at how to make simple eBay REST API call using SSIS. For demo purpose we will use Sandbox API rather than production.

  1. First download and install SSIS PowerPack from here.
  2. Create new SSIS Project and open SSIS Package
  3. Drag new ZS REST API Task from SSIS Toolbox on control flow designer
  4. Rename ZS REST API Task to Create eBay inventory item-1. Double click the task to configure it.
  5. Select Request mode to [Url from Connection]
  6. Select ZS-OAUTH connection type from connection dropdown.
  7. On OAuth connection. Select eBay Sandbox from provider dropdown.
  8. Enter App ID in Client ID field (Obtained from here)
  9. Enter Cert ID in Client Secret field  (Obtained from here)
  10. Select desired Scopes or leave it blank to use default scopes. For this example we need following two scopes
    https://api.ebay.com/oauth/api_scope/sell.inventory
    https://api.ebay.com/oauth/api_scope/sell.inventory.readonly
  11. Click on Advanced Tab. Enter RuName in the Return URL field. Don’t worry if your RuName doesn’t look like URL :) (Click here to learn how to get RuName – Scroll to Getting the RuName value)
  12. Now click on first tab again. Click Generate token button. When prompted login using Sandbox User Account (Click here to create test user for Sandbox).
    NOTE: Production account or developer account wont work when you login for eBay Sandbox API Token
  13. Click Accept button after login. If everything goes well you will see two tokens populated on your main screen (Access Token and Refresh Token). You will get prompt to save tokens to some backup file… Do that so same tokens can be used if you wish to deploy JOB to another machine.
  14. Click Test to validate connection. Click OK to save.
    SSIS OAuth Connection Manager - Connect to eBay Sandbox API

    SSIS OAuth Connection Manager – Connect to eBay Sandbox API

  15. Now once you close connection you are back to REST API Task
  16. Enter following URL to create or new eBay inventory item using PUT. Last part of following URL is ID we want to create
  17. Select Method PUT from dropdown
  18. Click Edit for Request Body. Enter following data for our sample product
  19. Change Body content type to JSON from dropdown
  20. Click on [Raw Edit] button in Headers grid toolbar. Enter following headers and click OK.
     
  21. Click Test Request/Response – If you get success that means product got created.SSIS Rest API Task - Call eBay REST API to create new inventory item
  22. You can create more products by simply changing SKU in URL and name/description in Body. Click Test to execute API call.

Read data from eBay and load into SQL Server using SSIS JSON Source

Now lets look at how to consume eBay REST API and load data into SQL Server. For this you can use JSON Source Connector (File, REST API)

SSIS JSON Source Connector - Get data from eBay REST API (Use OAuth Connection)

SSIS JSON Source Connector – Get data from eBay REST API (Use OAuth Connection)

Load eBay data into SQL Server - Call  eBay REST API using SSIS JSON Source

Load eBay data into SQL Server – Call eBay REST API using SSIS JSON Source

Pagination for eBay REST API Calls

Many eBay API provides two parameters for pagination. limit and offset. You can use Pagination option of JSON Source to control looping. Check this link for similar pagination approach you can use in eBay API.

User Token vs Application Token

Any time you call eBay API you have to supply token. eBay OAuth supports two types of token. In this article we used User Token. If you wish to use Application Token then you have to use OAuth Client Credentials grant. See this article

Difference between eBay Application Token vs User Token

  1. Application Token
    – Do not require user login
    – Use this token if you need to perform basic operations which are supported by following permission   https://api.ebay.com/oauth/api_scope
  2. User Token
    – Requires one time user login to create token and then refreshes automatically without login prompt (if you using SSIS PowerPack)
    – Use this token if you need higher privileges behalf of actual use for update/write data using API call

Use Application token when you want to perform readonly operations and you don’t need higher privileges (such as admin access). Certain eBay API calls only possible if you supply user token.

 

Conclusion

REST API integration typically requires coding and good understanding of protocol such as OAuth. SSIS PowerPack REST API connectors and OAuth Connection provides easy to use interface with many options to integrate virtually any API including eBay REST API inside your SQL Server Environment without any coding. Try SSIS PowerPack yourself see what is possible.

Posted in eBay REST API, REST API and tagged , , , , , , .