Scheduling API Pulls via SSIS

Scheduling API Pulls via SSIS: A Comprehensive Guide

Introduction
Overview of SSIS
Why Schedule API Calls?
Prerequisites
Understanding the API Endpoint
Creating the SSIS Project
Making API Requests in SSIS
- Using Script Component (C#)
- Using Web API Task (3rd Party)
Storing Data in SQL Server
JSON Parsing and Transformation
Scheduling the Package via SQL Server Agent
Error Handling and Logging
Advanced Scenarios (Pagination, OAuth, Headers)
Best Practices
Summary

1. Introduction

In modern data integration, businesses often need to fetch data from external services through REST APIs. Scheduling these API pulls enables consistent, automated data ingestion into SQL Server databases. SSIS (SQL Server Integration Services) is a powerful ETL tool that allows developers to design such automated workflows effectively.

This guide explores how to schedule API pulls using SSIS, from initial setup to deployment and error handling, in a step-by-step manner.

2. Overview of SSIS

SSIS is a platform for data integration and workflow applications. It is part of Microsoft SQL Server and allows you to:

Extract data from various sources
Transform it using built-in or custom components
Load the transformed data into a destination like SQL Server

You can schedule SSIS packages using SQL Server Agent to run at fixed intervals.

3. Why Schedule API Calls?

Scheduled API pulls are important for:

Syncing external data with your internal systems
Ensuring up-to-date reporting and dashboards
Automating data pipelines for data warehousing
Reducing manual processes and human error

Common API sources include:

Financial data (e.g., exchange rates)
Marketing platforms (e.g., Google Analytics, Facebook)
CRM systems (e.g., Salesforce, HubSpot)
E-commerce platforms (e.g., Shopify, WooCommerce)

4. Prerequisites

Before you begin, make sure you have:

SQL Server (2016 or later recommended)
SSIS installed via SQL Server Data Tools (SSDT)
Basic knowledge of C# and T-SQL
API documentation (endpoint, authentication method, parameters)
SQL Server Agent running (for scheduling)

Optional but helpful:

JSON Viewer or Postman for testing API endpoints
SSIS third-party tasks (e.g., ZappySys REST API Task)

5. Understanding the API Endpoint

Obtain details of the API:

Base URL: https://api.example.com/data
Method: GET (or POST)
Headers: e.g., Authorization: Bearer <token>
Query Parameters: e.g., ?start_date=2024-01-01&end_date=2024-01-31
Pagination: Does the API return data in pages?
Rate Limits: How often can it be called?
Response Format: Typically JSON

6. Creating the SSIS Project

Step-by-Step:

Open Visual Studio with SQL Server Data Tools.
Create a new Integration Services Project.
Add a Data Flow Task to the Control Flow.
Name the task (e.g., “Pull API Data”).

7. Making API Requests in SSIS

Option 1: Using Script Component

In the Data Flow Task, add a Script Component as a source.
Double-click to configure it as a source.
Choose C# as the language.

Sample C# Code for API Request:

using System.Net;
using System.IO;
using System.Web.Script.Serialization;

public override void CreateNewOutputRows()
{
    string url = "https://api.exchangerate-api.com/v4/latest/USD";
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    request.Method = "GET";

    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
    {
        string json = reader.ReadToEnd();
        Output0Buffer.AddRow();
        Output0Buffer.JsonData = json;
    }
}

Add a column (e.g., JsonData) to the output.
Connect this script output to the destination (e.g., SQL Server).

Option 2: Using Web API Task (ZappySys)

Drag and drop the ZappySys REST API Task
Configure the URL, headers, and method
Set response output to variable
Use Data Flow Task to write the variable to SQL Server

8. Storing Data in SQL Server

Create a table to store raw JSON or parsed data:

CREATE TABLE ApiData (
    Id INT IDENTITY(1,1),
    JsonResponse NVARCHAR(MAX),
    RetrievedAt DATETIME DEFAULT GETDATE()
);

In SSIS, use OLE DB Destination to insert JSON data from the Script Component into this table.

9. JSON Parsing and Transformation

After storing the JSON response, you can parse it using T-SQL:

SELECT
    JSON_VALUE(JsonResponse, '$.base') AS BaseCurrency,
    JSON_VALUE(JsonResponse, '$.rates.EUR') AS EuroRate,
    RetrievedAt
FROM ApiData;

Use OPENJSON if dealing with arrays:

SELECT *
FROM OPENJSON(@JsonResponse, '$.rates')
WITH (
    Currency NVARCHAR(10),
    Rate FLOAT
);

10. Scheduling the Package via SQL Server Agent

Deploy the package to SQL Server (SSISDB).
Open SQL Server Management Studio (SSMS).
Go to SQL Server Agent > Jobs > New Job.
Define the Job Name (e.g., “API Data Pull”).
Add a new Step:
- Type: SSIS Package
- Choose Package Source: SSISDB
- Select your package
Add a Schedule:
- Set frequency (e.g., daily, hourly)
Enable Alerts for failure notifications.

11. Error Handling and Logging

Use Event Handlers in SSIS to capture errors:

OnError: Write to log table or file
OnTaskFailed: Send email using SMTP Task

In Script Component:

try {
    // API call
} catch (Exception ex) {
    Dts.Events.FireError(0, "Script", ex.Message, "", 0);
}

12. Advanced Scenarios

Pagination:

Some APIs paginate results:

Use While loop in C# to iterate over pages
Append query parameter: ?page=1, ?page=2, etc.

OAuth Authentication:

If the API uses OAuth:

Obtain token in one Script Task
Pass token via variable to another Script Task that fetches data

Headers:

Add custom headers like:

request.Headers.Add("Authorization", "Bearer " + token);
request.Headers.Add("Accept", "application/json");

13. Best Practices

Use variables for API URLs and tokens
Log raw JSON for troubleshooting
Version control your packages
Encrypt sensitive data (like API keys)
Validate JSON structure before parsing
Implement retry logic for failed API calls
Monitor SSIS job executions regularly

14. Summary

Scheduling REST API pulls via SSIS provides a robust solution for automating data integration. By leveraging Script Components or third-party tools, you can fetch, parse, and store API data into SQL Server seamlessly. With proper error handling, scheduling, and best practices, SSIS can power your enterprise data workflows effectively.

Next Steps:

Expand to multiple APIs
Build a data warehouse using the fetched data
Integrate with Power BI for live reporting

Would you like sample SSIS packages or a downloadable PDF version of this guide?