How to call eBay REST API in SQL Server with 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.

	REST API Task
	OAuth Connection Manager
	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 🙂
6. Any API calls described on this article can be tested using Official eBay API Test tool here.

Test Ebay API using Online API Testing Tool

Before you can dig deep down we suggest you to try few API calls using ebay online testing tool. Here is an example api call using their Online Testing Tool.

For example visit below URL to try sample Search API call.

Testing Ebay REST API using Online Tool

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

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
    
    https://api.sandbox.ebay.com/sell/inventory/v1/inventory_item/GP-Cam-01

    1	https://api.sandbox.ebay.com/sell/inventory/v1/inventory_item/GP-Cam-01

17. Select Method PUT from dropdown
18. Click Edit for Request Body. Enter following data for our sample product
    
    { "availability" : { "shipToLocationAvailability" : { "quantity" : 50 } }, "condition" : "NEW", "product" : { "title" : "GoPro Hero4 Helmet Cam", "description" : "New GoPro Hero4 Helmet Cam. Unopened box.", "aspects" : { "Brand" : ["GoPro"], "Type" : ["Helmet/Action"], "Storage Type" : ["Removable"], "Recording Definition" : ["High Definition"], "Media Format" : ["Flash Drive (SSD)"], "Optical Zoom" : ["10x"] }, "imageUrls" : [ "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1000.jpg", "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1001.jpg", "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1002.jpg" ] } }

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

    { "availability" : { "shipToLocationAvailability" : { "quantity" : 50 } }, "condition" : "NEW", "product" : { "title" : "GoPro Hero4 Helmet Cam", "description" : "New GoPro Hero4 Helmet Cam. Unopened box.", "aspects" : { "Brand" : ["GoPro"], "Type" : ["Helmet/Action"], "Storage Type" : ["Removable"], "Recording Definition" : ["High Definition"], "Media Format" : ["Flash Drive (SSD)"], "Optical Zoom" : ["10x"] }, "imageUrls" : [ "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1000.jpg", "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1001.jpg", "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1002.jpg" ] } }

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.
    
    Content-Language: en-US Accept: application/json

    1 2	Content-Language: en-US Accept: application/json

     
21. Click Test Request/Response – If you get success that means product got created.
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). For this demo we will Call eBay Search API . If you want to explore other API then check this page. Here is

1. Go to Data flow tab
2. Drag and drop ZS JSON Source from SSIS Toolbox
3. Double click to configure it.
4. Enter API URL like below. We added Search parameter and limit parameter (Max rows per response). Refer to API Help page to know more about other available parameters.
   
   https://api.sandbox.ebay.com/buy/browse/v1/item_summary/search?q=phone&limit=100

   1	https://api.sandbox.ebay.com/buy/browse/v1/item_summary/search?q=phone&limit=100

5. Check Use Credentials option.
6. Select ZS-OAUTH connection or Create New (See previous section to create new one)
7. Select Filter (Look for Array Icon and select that node). For example if you calling search API then  enter or select  $.itemSummaries[*]  like below.
   

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

8. Now click on pagination tab and enter $.next  expression for next link attribute as below. This will enable pagination (It will keep reading in 100 rows per page until last page reached – Empty  recordset)
   

   Ebay REST API Pagination (Search API Example)

9. That’s it, now click Preview to see data
10. Once all looking good you can click OK to save JSON Source.
11. In Next Section we will see how to load Ebay Data into SQL Server.

Loading eBay data into SQL Server

Pagination for eBay REST API Calls

In previous example we saw REST API Pagination using Next Link approach but some API may not use same approach in that case refer to this link.  We looked at Next Link approach in previous section, you can also use Method #2 (URL parameter  – Offset) but Next Link approach is more simpler.

Calling eBay XML format API – Finding / Trading / Shopping API

eBay converted most of their APIs to JSON format to use OAuth 2.0 standard but there few APIs still there which are not OAuth 2.0 standard. These includes eBay Trading API, eBay Finding API, eBay Shopping API. They all are still in XML format. Below section describe how to call these API. Use eBay API Test tool here to generate desired Request Body and Headers.

Now let’s look at an example to call eBay XML API (GetItem from Trading API). To support special case like eBay API ZappySys introduced OAuth connection property called CustomAuthHeader in v2.6.7 or higher (Only found in Properties grid view).

1. Right click on OAuth connection manager icon > Click Properties > Set CustomAuthHeader to X-EBAY-API-IAF-TOKEN
   
2. Now drag ZS REST API Task from Control flow SSIS Toolbox
3. Set Properties like below
   1. Set URL as below (Sandbox Url is different than Production)
      
      https://api.sandbox.ebay.com/ws/api.dll

      1	https://api.sandbox.ebay.com/ws/api.dll

   2. Set Body As (this was obtained from  eBay API Test tool here )
      
      <?xml version="1.0" encoding="utf-8"?> <GetItemRequest xmlns="urn:ebay:apis:eBLBaseComponents"> <ErrorLanguage>en_US</ErrorLanguage> <WarningLevel>High</WarningLevel> <!--Enter an ItemID--> <ItemID>1234</ItemID> </GetItemRequest>

      1 2 3 4 5 6 7

      <?xml version="1.0" encoding="utf-8"?> <GetItemRequest xmlns="urn:ebay:apis:eBLBaseComponents"> <ErrorLanguage>en_US</ErrorLanguage> <WarningLevel>High</WarningLevel>       <!--Enter an ItemID-->   <ItemID>1234</ItemID> </GetItemRequest>

   3. Set Headers as below (Click on Raw Edit Button and paste below heades). Again these header were also copied from eBay API Test tool here . Notice that API function name is included in the header. Also site id and compatibility level required.
      
      X-EBAY-API-CALL-NAME: GetItem X-EBAY-API-SITEID: 0 X-EBAY-API-COMPATIBILITY-LEVEL: 967

      1 2 3

      X-EBAY-API-CALL-NAME: GetItem X-EBAY-API-SITEID: 0 X-EBAY-API-COMPATIBILITY-LEVEL: 967

   4. Now click OK to get response. If things working you will see some valid XML.
      

      Call eBay XML API using SSIS REST API Task (eBay Trading API, Shopping API, Finding API using OAuth Token)

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.

POST data to Ebay (Insert or Update)

In previous section we have seen how to read data from ebay. Now let’s look at how to write data to ebay.

There are two ways you can achieve this.

1. Use Web API Destination in Data Flow
2. Use REST API Task in Control Flow

Read this article to learn more about API POST using SSIS

Using SSIS Web API Task (Write data from SQL Server to ebay)

Using REST API Task to POST data to ebay

If you have JSON/XML data already prepared and if you like to POST it to ebay API then REST API Task would be easy to use.

REST API Task Body can be direct string, variable (e.g. {{User::varSomeData}} or Body can come from File 

Common Errors

Deployment to Production

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.