Real-Time Data Retrieval from APIs Using SSIS

Introduction

These days, most of the businesses rely on Application Programming Interfaces (APIs) to have external data sources and to facilitate that, integrate with various source systems to automate workflows.

As we know,w SQL Server Integration Services (SSIS) have the capability to integrate with API’s. Also, it has capabilities to automates the ETL process instead of manual data extraction, transformation, and loading hence business started leveraging SSIS to achieve this. SSIS also provides error handling and logging capabilities for robust automation.

To automate data retrieval from APIs, SSIS establishes a secure connection with API using HTTPs request and extract real-time or scheduled data from the API in JSON or XML format.

First, let's understand what an is API.

API, which translates to Application Programming Interface, provides platform to different software applications to interact with each other. It's primarily a software middle-ware that facilitates data exchange among applications.

Now let’s understand this in detail with an example.

We have the below API service (hosted locally) running that we are going to integrate and read data through SSIS.

Now, let’s open SQL Server Data Tools (SSDT) and create a new SSIS package “Automate_ETL_Pipeline”. Now in the Control Flow, drag and drop a Script Task that we can see on below screen shot.

Now, let’s configure global parameters at the Project. parameter for the ETL SSIS project in Solution Explorer. The following parameter was added.

• Name: ApiUrl
• DataType: String
• Value: http://localhost:5000/api/Account

Parameter added in the Project. The parameter can be used for all the SSIS packages created under this project. So, if any other package needs the ApiUrl parameter, it can use it directly.

The configured parameter can be seen on the screenshot below, highlighted in red.

Now let’s double-click on the Script Task to open the editor and configure the API connection. Configured ReadOnlyVariables and selected project parameters $Project::ApiUrl that can be seen on below screenshot.

Now, double-click on Edit Script on the Script Task Editor to write code using Visual C# 2022.

Below C# code we are going to add in the Script Task Editor.

public void Main()
{
    // TODO: Add your code here
    string serviceUrl = Dts.Variables["$Project::ApiUrl"].Value.ToString();

    HttpClient client = new HttpClient();
    client.BaseAddress = new Uri(serviceUrl);

    // Add an Accept header for JSON format.
    // client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    var response = client.GetAsync(serviceUrl).Result;
    if (response.IsSuccessStatusCode)
    {
        MessageBox.Show(response.Content.ReadAsStringAsync().Result);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

After clicking on Edit Script, it will open Microsoft Visual Studio Tools (VSTA), where we write C# code. Now, within the Main () function, we will add our C# code written above.

Script Task configuration is done, we executed the package (in debugging mode), and can see JSON data populated in the below screenshot.

Now the package executed successfully, as we can see in the screenshot below. We can switch this to design mode if we want to do any fix by clicking on the populated blue link.

Conclusion

In today’s article, we went through the Script task and learned how to read API Data using SQL Server Integration Services (SSIS). This helps business on streamlining data pipelines, connect APIs without much efforts. Hope you liked the article, please share your comments/ suggestions below.