25 Mar, 2025

Copy Data from API to SQL Server using Azure Data Factory

Posted on 25 Mar, 2025 by Ankit Gore, Posted in Azure , Microsoft 365

Blogs

Introduction:

In today's data-driven world, automating data flow between APIs and databases is crucial for efficient data management. Azure Data Factory (ADF) provides a powerful solution to extract, transform, and load (ETL) data seamlessly.

This blog walks you through a step-by-step process of integrating a REST API with an Azure SQL Database using ADF. You'll learn how to:

• Create Linked Services for API and SQL Server.
• Build a Pipeline with a Copy Data activity.
• Map API fields to SQL table columns.
• Run and verify the data transfer.

By the end of this guide, you'll be able to automate API data ingestion into SQL Server, making data readily available for reporting, analysis, and business intelligence. Let's dive in!!!
 

Prerequisites:

Before you start, ensure you have the following:

1. Azure Subscription

• An active Azure account with access to Azure Data Factory (ADF).
• If you don’t have one, you can create a free Azure account.

2. Azure Data Factory Instance

• An ADF instance already set up in your Azure portal.
• If not, create one by navigating to Azure Portal > Data Factory > Create a new instance.

3. SQL Server & Database

• An Azure SQL Database or an on-prem SQL Server with firewall rules configured for ADF access.
• A database table ready to receive the API data (or permissions to create one).

4. API Endpoint

• A REST API to fetch data from.
• For this tutorial, we'll use JSONPlaceholder API (a free test API).

5. Required Permissions

• Azure Data Factory Contributor role to create and manage pipelines.
• Database write access to insert API data into SQL tables.

Once you have these ready, you're all set to start the integration process!!

Process:

Step 1: Create a Linked Service for the API

1. Open ADF Studio.
2. Navigate to Manage > Linked Services > New.
3. Search for HTTP and select it.

4. Configure the following settings:
Base URL: https://jsonplaceholder.typicode.com/
Authentication: Anonymous

5.Test the connection and create the linked service.

 

Step 2: Create a Linked Service for SQL Server

1. Go to Manage > Linked Services > New.
2. Search for Azure SQL Database or SQL Server.

3. Enter the server details and authentication credentials.

4. Test the connection and create the linked service.
 
Publish the Changes.

 

Step 3: Create Pipeline & Copy Data Activity.

1. Create a Pipeline


Add the Copy Data Activity.


2. Configure the Copy Data Activity

API Dataset (Source)

1. Choose REST or HTTP and select the API Linked Service.

2. Set Relative URL: users

 

3. Click Preview to check if the API is working.

4. The JSON output should be visible.

Publish the changes.


5. Create Table in SQL Database

          (Assuming the SQL database is already created.)
     

SQL Server Dataset (Sink)

1. Choose Azure SQL Database or SQL Server.
2. Select the SQL Linked Service and choose the destination table.

3. Update the sink settings and publish the changes.

 

Step 4: Mapping API Fields to SQL Table Columns

1. Click on Mapping.
2. Click Import to fetch the API fields.

3. The mapped data will be visible.

4. Delete any unwanted fields.

Publish the changes.
 

Step 5: Run the Pipeline

1. Click Debug to test the pipeline.

2. Check the output in SQL Server to verify the data transfer.

 

Hope this was useful! Stay tuned for more interesting blogs like this.