Read / Write Shopify data in SSIS (REST API)

Introduction

In our previous article, we saw how to call REST API in SSIS. Now in let’s use that knowledge and learn how to read/write Shopify data in SSIS. If you are not aware of Shopify then its one of the most popular eCommerce platforms out there for small shops who sell online. Shopify provides a total framework to sell/fulfill orders online including tools to build/customize your own web store.

In this article we will see few examples on how to call basic REST API calls to read shopify data, delete shopify data and write shopify data using various SSIS Components.

Create Shopify Custom App for Shopify Admin API

Before accessing Shopify API you must create Custom App (and then obtain Access Token)

New approach (Custom App) to generate App Credentials for Rest API Call

Before you call Shopify REST API, your first step is to obtain credentials. We will need Access Token or Storefront Access Token. We will use it in later section to call Shopify REST API.

1. First, in Shopify, go to sore Settings (Click Gear Icon) >  Apps and sales channels > click Develop apps for your Store
   OR just navigate to URL
   
   https://{your-store}.myshopify.com/admin/settings/apps/development

   1	https://{your-store}.myshopify.com/admin/settings/apps/development

   

   Shopify App and Sales Channel

    

2. In App development, click on Create an app
   
   

   Shopify Create an App

    

3. Provide the appropriate name for your custom app.
   

   Shopify Custom App Name

    

4. Once we created the app name, we have 2 options to create Admin API or Storefront API in the Overview tab.                                                                               Shopify App Scopes and Configuration
5. Click on Configure Admin API scopes or Configure Storefront API scopes based on your need as shown in the above screenshot.
6. If click on Configure Admin API scopes, then select the appropriate scopes for your Admin API.
7. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.
8. In addition, change the products, variants, and collections to read and write if you need to create, update and delete products.
   

   Shopify Admin Api Scope

    

9. After saving it, it asks to install app and generate Shopify Access Token for your Admin API.
   

   Shopify Admin Install App

    

   

   Shopify Admin Access Token

10. If click at Configure Storefront API scopes, then select the appropriate scopes for your Storefront API.
11. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.
12. In addition, change the products, variants, and collections to read and write if you need to create, update and delete products.
    

    Shopify Storefront API Scope

     

13. After saving it, it asks to install app and generate Shopify Storefront Access Token for your Admin API shown above screenshot.
    

    Shopify Storefront Install App

     

     

    

    Shopify Storefront Access Token

     

14.  We have created Admin API Access Token and Storefront API Access Token to access the Shopify APIs.
    

    Shopify API Credentials

Old approach (Private App) to generate App Credentials for Rest API Call

Before you call Shopify REST API, your first step is to obtain credentials. We will need API Key and Password. We will use it in later section to call shopify REST API.

1. First, in Shopify, go to Apps and click Manage private apps.
   

   Shopify App – Manager private apps

2.  Secondly, In Private apps, click the Create a new private app:
   

   Shopify Private apps

3. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.
4. In addition, change the products, variants, and collections to read and write if you need to create, update and delete products.
   

   Permission for private apps.

5. Finally, you will have an API key, a password, an URL and a shared  secret that you will use in the next section:

Shopify API key

Read Shopify data in SSIS (Get Orders Example)

So in this section we will learn how to call Shopify REST API.  For example purpose we will call Shopify Orders Endpoint. For all other REST API endpoints check this link. Read carefully which parameters you can pass for each API call. So let’s read shopify orders using sample API call.

Configure SSIS JSON Source to read from Shopify

Now, that we created an App in Shopify, the rest is easy.

1. First, in an SSIS project, drag and drop the Data Flow and double click it.
   

   Use the Data Flow

2. Secondly, in the Data Flow, drag and drop the ZS JSON Source.
   

   SSIS JSON Source – Drag and Drop

3. Double-click on JSON Source to configure it.If you are not familiar with ZS JSON Source, check this link.
   New approach to establish the HTTP Connection Manager

   Using Admin API Access token

   1. Enter URL for the API you like to call. For Example, you can enter
      
      https://{your-store}.myshopify.com/admin/api/2022-10/products.json

      1	https://{your-store}.myshopify.com/admin/api/2022-10/products.json

   2. Check Use Credentials and click on NEW-HTTP connection as shown below,
   3. On HTTP Connection Manager you can enter some URL (it will be ignored and use JSON Source URL above)
   4. Choose Credentials Type as Static Token / API Key
   5. In the Token / Key text box enter your Admin API Key (obtained from the previous section)
   6. In the Auth Header Name enter X-Shopify-Access-Token
   7. In the Auth Scheme select {none} 
      

      SSIS JSON Source Admin API HTTP Connection Manager

       

4. Also, press the Select Filter button and press the orders node:
   

   SSIS Shopify filter

5. Now go to Pagination Tab and select RFC5988 Pagination mode as below. Check this link for more info on Shopify Paginated API calls.
   

   Link Header Pagination Method (RFC 5988)

   NOTE:  For Older API use Cursor-based pagination as below (version 2019-07 or older). 
   

   SSIS pagination for Shopify

6. Finally, make sure to the Use Credentials option and select the http connection of the step 2 and press the Preview button to see the data:

SSIS preview Shopify data

Load Shopify data into SQL Server / Other Target

Write Shopify data in SSIS REST API Task

In this new example, we will write Shopify data in SSIS. In addition, we will create a new order using REST API to write Shopify data in SSIS.

1. First, use the REST API task in SSDT.
2. Secondly, in the REST API use the http connection created on step 2 of the read Shopify data in SSIS.  Also, in HTTP Request Method, select POST, in Body (Request Data) write the following:
   
   JavaScript

   { "order": { "line_items": [ { "title": "Big Brown Bear Boots", "price": 74.99, "grams": "1300", "quantity": 3, "tax_lines": [ { "price": 13.5, "rate": 0.06, "title": "State tax" } ] } ], "transactions": [ { "kind": "sale", "status": "success", "amount": 238.47 } ], "total_tax": 13.5, "currency": "EUR" } }

   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 26 27 28

   {   "order": {     "line_items": [       {         "title": "Big Brown Bear Boots",         "price": 74.99,         "grams": "1300",         "quantity": 3,         "tax_lines": [           {             "price": 13.5,             "rate": 0.06,             "title": "State tax"           }         ]       }     ],     "transactions": [       {         "kind": "sale",         "status": "success",         "amount": 238.47       }     ],     "total_tax": 13.5,     "currency": "EUR"   } }

   

   Post JSON order Shopify

    

3. If you dont like to use URL supplied in HTTP Connection manager then just check override URL option and enter URL like below. For this example we used correct URL in HTTP connection manager so we will not override URL.
   https://{{your-store}}.myshopify.com/admin/orders.json
4. Now you can click on Test Request / Response and you will see one sample order created in your admin portal.
5. You can supply variables in Body by clicking on Insert Variable dropdown when you edit Body.

Calling Storefront API

In our previous section, we saw how to call ADMIN API which is the most common API you will use in most cases to read / write store data. However if you have a need to call Storefront API  instead then configure HTTP Connection as below

1. For Store Front API call you can follow the same steps as the previous section except for few things on HTTP Connection Manager UI
2. In API Key enter Storefront API Access Token (rather than Admin Token)
3. In the Auth Header Name enter X-Shopify-Storefront-Access-Token
   

   SSIS JSON Source Storefront HTTP Connection Manager

Legacy Approach – Using Basic Auth

If you created Shopify PrivateApp (before they introduced the new CustomApp approach) then your HTTP Connection needs to be like below. Use Basic Auth for Credentials Type

1. Using approaches to establish the HTTP Connection Manager 
   

   SSIS JSON Source HTTP connection

Create Multiple Shopify records using Web API Destination

With our ZS Web API destination, it is possible to create multiple records at the same time.  The following link shows the ZS Web API Destination information:

• SSIS Web API destination

Here are few useful articles to explain Web API destination use case.

How to POST data to URL

Few other articles

Delete Shopify records using REST API in SSIS

1. First, in SSDT, drag and drop the ZS REST API task.
2. Secondly, add the HTTP Url Connection.  Use the DELETE HTTP Request Method and the URL including the order id as below:
   https://{{your-store}}.myshopify.com/admin/orders/{{orderid}}
   

   SSIS delete data in Shopify using REST API call

    

3. Now click Test Request / Response to test delete API call.

Conclusion

In this article, we learned how to create a Shopify app. In addition, we learned how to connect to Shopify using SSIS with the ZappySys tools. Also, we learned to use ZappySys tools in SSIS to get, write and delete information.

References

Finally, for more information, refer to these links:

• ZappySys SSIS JSON Source
• ZappySys SSIS REST API task