How to query GraphQL API in SSIS with Pagination

[nayan]

GraphQL is getting traction these days so possible that your API Provider might expose API as GraphQL. In this Post you will learn how to query GraphQL API in SSIS and Paginate API using SSIS JSON Source in a few clicks.

Basic API call for GraphQL

Here is an example of GraphQL API call to fetch product information using Sopify GraphQL API

Sample First Request (query - not encoded for simplicity - see next section for actual body)

POST  https://{your-shop}.myshopify.com/admin/api/2023-10/graphql.json
X-Shopify-Access-Token: shpat_296exxxxxxxxxxxxxxxxxxxxxxx
Content-Type: application/json

{
    "query": "{
      products(first: 3) {

          nodes {
            id
            handle
          }

          pageInfo {
             hasNextPage
             endCursor
          }

      }
    }"
  }

Sample Response

{
    "data": {
        "products": {
            "nodes": [
                {
                    "id": "gid://shopify/Product/7349685485668",
                    "title": "Some Product-A",
                    "createdAt": "2023-12-13T22:05:13Z"
                },
                {
                    "id": "gid://shopify/Product/7349685518436",
                    "title": "Some Product-B",
                    "createdAt": "2023-12-13T22:05:14Z"
                },
                {
                    "id": "gid://shopify/Product/7349685551204",
                    "title": "Some Product-C",
                    "createdAt": "2023-12-13T22:05:15Z"
                }
            ], "pageInfo": {
                "hasNextPage": true,
                "endCursor": "eyJsYXN0X2lkIjoxxxxxxxxxxxxxxxxxxx"
            }
        }
    }
}

Sample Next Request (Paginated response)*

POST  https://{your-shop}.myshopify.com/admin/api/2023-10/graphql.json
X-Shopify-Access-Token: shpat_296exxxxxxxxxxxxxxxxxxxxxxx
Content-Type: application/json

{
    "query": "{
      products(first: 3, after: "eyJsYXN0X2lkIjoxxxxxxxxxxxxxxxxxxx") {

          nodes {
            id
            handle
          }

          pageInfo {
             hasNextPage
             endCursor
          }

      }
    }"
  }

Sample Last Response
In the last response you will get hasNextPage : false.

{
    "data": {
        "products": {
            "nodes": [
                {
                    ....
                },
                {
                    ...
                },
                ....
            ], "pageInfo": {
                "hasNextPage": **false**,
                "endCursor": "eyJsYXN0X2lkIjoxxxxxxxxxxxxxxxxxxx"
            }
        }
    }
...
}

For clarity, we did not encode new line / double quote characters in the above query attribute. Below is the actual body you need to send from any REST API tools like the CURL command line, ZappySys JSON Source, Postman. In later section we will show you how we use FUN_JSONENC function to encode string so its a valid JSON Body (new line, double quote, slash etc needs to be escaped for valid JSON). Yes below string is not pretty … see new lines replaced by \n. Same for a double quote " with " and \ with \ else you will get an error.

if you use the GraphQL option in some tools then it will auto-encode for you so you do not need to worry about encoding manually - New version of Postman does this way if you choose GraphQL option

POST  https://{your-shop}.myshopify.com/admin/api/2023-10/graphql.json
X-Shopify-Access-Token: shpat_296exxxxxxxxxxxxxxxxxxxxxxx
Content-Type: application/json

{
 "query" : "{ products(first: 200  after: null ) \n {\n    nodes {\n      id\n      title\n      createdAt\n    }\n    \n    pageInfo {\n      hasNextPage\n      endCursor\n    }\n    \n  }\n}"
}

Step-By-Step - Call GraphQL in SSIS with Pagination (Shopify API example)

Now let’s look at how to call Sopify GraphQL API in SSIS. Concepts should remain the same for any GraphQL API but we using Shopify API for the demo.

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished the first step, Open Visual Studio and Create a New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
4. Double click on the DataFlow task to see DataFlow designer surface.
5. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
   
6. Double click JSON Source and configure the following pieces URL, Connection, Advanced Pagination Tab Check Enable Page Token for Body
   Enable Page Token Option for GraphQL - Shopify API|690x477
7. Now go to the Settings Tab.
8. Change request method to POST
9. Change Body Content Type to application/json
10. Click edit next to Body and Enter something like below. Change necessary parts as per your GraphQL need. Mainly you need to change page size (e.g. max 200 rows per page in this example), table name (e.g. products) and column names you like to fetch.

{
 "query" : "{ products(first: 200  after: <<[$pagetoken$]|~|null|~|\"$1\",FUN_IF_EMPTY>> )<< 
 {
    nodes {
      id
      title
      createdAt
    }
    
    pageInfo {
      hasNextPage
      endCursor
    }
    
  }
} 
 ,FUN_JSONENC>>"
}

11. In the Array Filter enter the expression like below. Change the Table name as per your need (e.g. products to something else). You can also click Browse button to select the same but remove [*] from the end after selection.

$.data.products.nodes

12. Now click on the Pagination Tab and select the below settings
    

    

13. That’s it now you can click Preview and click OK to save
    

    

14. Connect your source to some target like Upsert Destination and Load GraphQL API data into SQL Server or other database.
    

    

15. Run the data flow and you can see more than one paginated request made (use tool like fiddler to see API traffic)