How to Call Xelion REST API in a Paginated Manner Using SSIS

SSIS PowerPack JSON
1

Introduction

In this article, we’ll walk through the steps to call the Xelion paginated API using SQL Server Integration Services (SSIS). We’ll cover the configuration of the JSON Source component to handle pagination, filtering records between two dates, and processing the data.

Prerequisites

Ensure you have the following installed:

  1. SQL Server Data Tools (SSDT): Install SQL Server Data Tools, the development tool for SSIS.
  2. ZappySys SSIS PowerPack: Download and install the ZappySys SSIS PowerPack.
  3. Access to the Xelion API and the necessary API credentials.

Step-by-Step Guide

1. Set Up SSIS Package

First, create a new SSIS package in SQL Server Data Tools (SSDT).

2. Add a Data Flow Task

Drag and drop a Data Flow Task onto the Control Flow design surface and name it “Retrieve Xelion Communications”.

3. Configure the JSON Source Component

Inside the Data Flow Task, drag a JSON Source component from the SSIS Toolbox onto the design surface. This component will call the Xelion API and handle the pagination.

4. Configure API Request URL

Double-click the JSON Source component to open its editor. In the Connection Manager tab, set up a new HTTP Connection Manager with the following properties:

  • Request URL: https://9.pbx.infopact.nl/api/v1/dtc/communications?limit=100&order=descending
  • Method: GET
5. Configure Pagination

Switch to the Pagination tab and configure it as follows:

  • Next Link/Cursor Expression: $.meta.paging.previousId
  • Stop Indicator Attribute: $.meta.paging.previousId
  • Stop Indicator Value/Regular Expression: regex=^$
  • Suffix for Next URL: before=<%nextlink%>

This configuration uses the previousId from the API response to fetch the next set of records until there are no more records to retrieve.

6. Apply Date Filters

To retrieve records between two dates, you need to modify the Request URL to include from and until query parameters. You can do this by adding SSIS variables and using expressions to construct the URL dynamically.

a. Create SSIS Variables
  • FromDate: DateTime, set to the desired start date.
  • UntilDate: DateTime, set to the desired end date.
  • RequestURL: String, set to the initial API URL.
b. Use Expressions to Construct URL

In the JSON Source component, go to the Expressions tab and set the RequestURL expression to:

"https://9.pbx.infopact.nl/api/v1/dtc/communications?limit=100&order=descending&from=" + (DT_WSTR, 20) @[User::FromDate] + "&until=" + (DT_WSTR, 20) @[User::UntilDate]
7. Map the Output

Map the JSON response fields to SSIS columns in the Columns tab of the JSON Source component.

8. Process the Data

Connect the JSON Source component to other SSIS components to process the retrieved data, such as a Data Conversion transformation if needed, and then load the data into a destination (e.g., SQL Server).

9. Execute the Package

Save and execute the SSIS package to retrieve and process the paginated data from the Xelion API.

Sample Screenshots

Below are the sample screenshots of the JSON Source component configuration, including the Request URL setup and Pagination tab, to provide visual guidance.

JSON Source configuration
JSON Source configuration935×828 53.8 KB

Package execution

Execution Logs to see the Paginated call URLs
Execution Logs to see the Paginated call URLs950×881 129 KB

Conclusion

By following these steps, you can successfully call the Xelion paginated API using SSIS, apply date filters to retrieve records between specific dates and process the data efficiently. This method ensures that your data extraction is automated and handles pagination seamlessly.