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.

Step 1: Create a Table in Azure SQL Database

Azure SQL Database is a fully managed Platform as a Service (PaaS) relational database that offers high availability, scalability, and security. Before you can use ADF to insert or retrieve data, you must have at least one table set up in your database. Here’s how to create one quickly:

Ways to Create a Table

• Query Editor in Azure Portal: Ideal for quick testing and prototyping, accessible directly from the Azure Portal.
• Azure Data Studio: A cross-platform database tool for professionals who need advanced capabilities.
• SQL Server Management Studio (SSMS): The traditional choice for comprehensive database management.

For demonstration, we’ll use the Query Editor in the Azure Portal, which is straightforward for beginners.

Instructions:

1. Sign in to the Azure Portal (https://portal.azure.com) and navigate to your SQL Database resource.
2. In the left menu, select Query editor (preview).
3. Authenticate using your SQL server admin username and password.
4. In the query window, enter the following SQL script:

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

1. Click Run to execute the script.

This script creates an IndustryData table with columns for an industry identifier, name, region, and revenue. You can modify the schema to match your particular use case and data requirements.

Once the script is executed successfully, your table is ready to store data.

Step 2: Configure a Linked Service in Azure Data Factory

With your table set up, the next step is enabling Azure Data Factory to communicate with your Azure SQL Database. This is achieved through a Linked Service, which stores the connection details securely in ADF.

A Linked Service functions like a connection string, securely holding the credentials and other parameters ADF uses to connect to your data sources.

Instructions:

1. Open your Azure Data Factory instance in the Azure Portal.
2. Select Manage (gear icon) from the left-hand navigation pane.
3. Under Connections, click Linked services.
4. Click + New to add a new Linked Service.
5. In the data store list, search for and select Azure SQL Database.
6. Fill in the required details:
   • Name: LS_AzureSQLDB
   • Server name: Find this on your SQL Database Overview page (e.g., myserver123.database.windows.net).
   • Database name: The name of your database (e.g., myazuresqldb).
   • Authentication type: SQL Authentication (use the admin credentials set during database creation).
   • Username & Password: Enter your SQL admin username and password.
7. Click Test connection. A successful test will show a green check mark.
8. Click Create to save the Linked Service.

Your Azure Data Factory is now securely connected to your Azure SQL Database, ready for data integration tasks.

What’s Next?

With the Linked Service established, you can now create a Dataset in ADF that references your IndustryData table. This dataset acts as a pointer, allowing you to easily use the table within pipelines. For example, you could set up a Copy Data pipeline to move data from a CSV file in Azure Blob Storage into your SQL table, or design Mapping Data Flows to transform and load data directly.

Conclusion

By following these steps, you have successfully learned how to create SQL Table and Linked Service in ADF.

• Created the IndustryData table in Azure SQL Database.
• Configured a Linked Service in Azure Data Factory for secure connectivity.

These foundational tasks are essential when you create SQL Table and Linked Service in ADF, enabling you to build reliable and scalable data pipelines. With your Linked Service established, you can orchestrate seamless data movement and transformation across cloud resources, supporting everything from simple ETL processes to enterprise-level data integration.

Watch the full video here: