How to Create a SQL Table and Linked Service in ADF

Azure Data Factory (ADF) is a leading solution for orchestrating data movement and transformation in modern pipelines. To get started, you must first Create SQL Table and Linked Service in ADF. These two prerequisites are essential:

• An Azure SQL Database table, which serves as either the source or destination.
• A Linked Service in ADF, which securely connects your ADF environment to the database.

This step-by-step guide will show you how to create SQL Table and Linked Service in ADF, so you can efficiently move and transform data within your pipelines.

1. Creating a Table in Azure SQL Database

Azure SQL Database is a fully managed relational database service. To enable ADF to read from or write to your database, you must first have at least one table set up.

Ways to Create a Table

• Azure Portal’s Query Editor
• Azure Data Studio
• SQL Server Management Studio (SSMS)

For quick setup, the Azure Portal’s Query Editor is the most straightforward.

Steps:

1. Log in to the Azure Portal and find your SQL Database.
2. Select Query editor (preview) from the left menu.
3. Authenticate with your SQL credentials.
4. Paste and run the following SQL script:

CREATE TABLE IndustryData (
    IndustryID INT PRIMARY KEY,
    IndustryName NVARCHAR(100),
    Region NVARCHAR(50),
    Revenue DECIMAL(18,2)
);

This creates a table named IndustryData with four columns: IndustryID, IndustryName, Region, and Revenue. Adjust the schema to match your requirements.

Once run, your table is ready for data.

2. Configuring an Azure Data Factory Linked Service

With your table created, you need to allow ADF to access your SQL Database. This is done by configuring a Linked Service—a secure connection definition in ADF.

A Linked Service in ADF acts as a secure bridge between ADF and your data resources, such as Azure SQL Database. Think of it as a managed connection string that lets ADF authenticate and communicate with your database without exposing sensitive credentials each time.

Steps:

1. Open your Azure Data Factory instance.
2. In the left menu, select Manage (⚙️).
3. Under Connections, choose Linked services.
4. Click + New.
5. Search for and select Azure SQL Database.
6. Complete the required fields:
   • Name: LS_AzureSQLDB
   • Server name: (Find this on your SQL Database’s Overview page, e.g., myserver123.database.windows.net)
   • Database name: e.g., myazuresqldb
   • Authentication type: SQL Authentication
   • Username & Password: Your database credentials
7. Click Test connection. You should see a green check mark if successful.
8. Click Create to finish.

Your Linked Service is now securely connected to your Azure SQL Database.

Next Steps After Creating SQL Table and Linked Service in ADF

With your Linked Service ready, you can create a Dataset in ADF that points to your IndustryData table and continue building your data solutions in Azure Data Factory. This serves as a reference for pipelines such as:

• Copy Data: Move data from sources like Azure Blob Storage into your SQL table.
• Mapping Data Flows: Transform and load data directly into SQL tables.

Conclusion

By completing these steps, you have:

• Created the IndustryData table in Azure SQL Database.
• Configured a secure Linked Service in ADF.

These foundational tasks set the stage for building robust data pipelines in Azure Data Factory, empowering you to orchestrate, copy, and transform data from a variety of sources into your SQL databases with confidence.

With these prerequisites in place, you’re now ready to take advantage of ADF’s powerful features—whether you’re building simple data movement solutions or designing complex, automated ETL pipelines. As you proceed, you’ll be able to integrate data from diverse sources, apply business logic, and ensure your data infrastructure is scalable, secure, and efficient. Embracing these best practices will help you unlock the full potential of your data in the Azure ecosystem and drive meaningful insights for your organization.

Watch the full video here: