Moving data between different storage systems is a fundamental task in cloud-based data engineering, and the Copy Data Activity in ADF provides a streamlined way to accomplish this. One of the most common scenarios involves copying data from a CSV file located in Azure Blob Storage into a table in Azure SQL Database.
In this guide, we’ll provide a comprehensive, step-by-step walkthrough of this process using the Copy Data Activity in ADF, ensuring clarity for both beginners and experienced users.
Prerequisites
Before you begin, make sure you have the following ready:
- An Azure SQL Database with a table named IndustryData.
- Linked Services already configured in ADF for both your Azure Blob Storage and Azure SQL Database.
With these prerequisites in place, let’s proceed to the detailed steps.
Step 1: Create Source and Sink Datasets
Datasets define the schema and location of your source and target data stores. You’ll need to create two datasets: one for the source CSV file in Blob Storage and another for the destination SQL table.
1.1 Source Dataset (Azure Blob Storage – CSV)
- In ADF’s authoring environment, navigate to the Manage hub to confirm your Blob Storage Linked Service exists.
- Go to the Author tab, click + New dataset, and select Azure Blob Storage as the data store type.
- Choose DelimitedText as the format since your source file is in CSV format.
- Name your dataset (e.g., ds_blob_industry_csv). Link it to your Blob Storage Linked Service.
- Browse to select the CSV file (e.g., industry.csv).
- Under the Settings tab, enable First row as header to ensure column names are recognized.
1.2 Sink Dataset (Azure SQL Database Table)
- Create a new dataset, choosing Azure SQL Database as the type.
- Connect it to your SQL Database Linked Service.
- Specify the table name (IndustryData).
- Name the dataset (e.g., ds_sql_industry_data).
With these datasets, ADF knows both the data’s origin and destination.
Step 2: Create and Configure the Pipeline
A pipeline in ADF defines the flow and transformation of data. Here, the main component is the Copy Data activity.
- In the Author tab, click + New pipeline to create a new pipeline.
- In the pipeline editor, search under Move & Transform for the Copy Data activity.
- Drag the Copy Data activity onto the pipeline canvas.
Configure the activity as follows:
Source Tab:
- Set the Source dataset to the previously created Blob CSV dataset (ds_blob_industry_csv).
- (Optional) Preview the data to ensure the correct file is referenced.
Sink Tab:
- Set the Sink dataset to the SQL table dataset (ds_sql_industry_data).
Mapping Tab:
- Click Import Schemas or Import Mapping. If the CSV headers and SQL table columns match, ADF will map them automatically. If not, adjust the mappings as needed to ensure each field is correctly aligned.
At this stage, your pipeline is configured to transfer data from the Blob Storage CSV to the Azure SQL Database table.
Step 3: Test, Deploy, and Run the Pipeline
With your pipeline configured, it’s important to validate its functionality.
- Click Debug in the pipeline editor to run the pipeline in test mode. This allows you to catch any configuration issues before official deployment.
- Monitor the run in the Output pane to ensure the process completes successfully without errors.
- Once testing is complete, click Publish All to save and deploy your pipeline changes.
- You can now manually trigger the pipeline or automate its execution using ADF triggers for recurring data integrations.
To confirm the data transfer, query your target table in Azure SQL Database:
SELECT * FROM IndustryData;
You should see the contents of your CSV file reflected in the SQL table, confirming a successful data transfer.
Conclusion: Why Use Copy Data Activity in ADF?
The Copy Data Activity in ADF enables seamless data movement across Azure services. With minimal configuration, you can automate ingestion of CSV data from Azure Blob Storage into Azure SQL Database. This approach is efficient, scalable, and ideal for both one-time loads and recurring ETL processes.
ADF ensures your data is consistent, centralized, and ready for analytics—making Copy Data Activity in ADF an essential tool for modern data engineering workflows.
Watch the full video here: