A Beginner’s Guide to Bulk Insert Copy Method in Azure Data Factory

/ ADF Bulk Insert Copy, ADF Copy Activity, Azure / By

Data is everywhere, and sometimes we need to move large amounts of it quickly. Azure Data Factory (ADF) helps us do that efficiently using the Bulk Insert Copy Method. This guide will explain what bulk insert is, why it’s important, and how to set it up step by step—all in simple, easy-to-understand language.

What Is Bulk Insert in Azure Data Factory?

Bulk Insert is like using a big shovel to move data quickly from one place to another. Instead of moving data row by row, it moves large chunks of data all at once. This method is much faster, especially when dealing with large datasets.

Why Use Bulk Insert?

  1. Speed: Transfers large amounts of data quickly by batching records.
  2. Efficiency: Reduces the time spent on moving data by minimizing the number of network calls.
  3. Cost Savings: Faster data transfers can reduce compute and storage costs.

When Should You Use Bulk Insert?

  • When moving large datasets between databases (e.g., SQL Server to Azure SQL Database).
  • When you need to quickly copy data into a data warehouse or large tables.
  • When you don’t need real-time data and can transfer in large chunks.

Step-by-Step Guide to Bulk Insert in Azure Data Factory

Prerequisites

  1. Azure Subscription: Make sure you have an active Azure account.
  2. Data Sources: Identify your source (e.g., on-premises SQL Server or Azure Blob Storage) and destination (e.g., Azure SQL Database).
  3. Azure Data Factory: Create or use an existing Azure Data Factory instance.

Step 1: Create Datasets for Source and Sink

  1. Log in to Azure Portal and open Azure Data Factory.

  2. Go to Author & Monitor to access the ADF UI.

  3. Click Manage on the left panel and then select Linked Services to create connections to your source and sink.

    • For Source (e.g., SQL Server, Blob Storage, etc.):
      1. Click + New to add a new linked service.
      2. Choose the source type (e.g., Azure Blob Storage, SQL Server).
      3. Configure the connection by providing the necessary credentials and test the connection.
    • For Sink (e.g., Azure SQL Database):
      1. Create another linked service for the sink.
      2. Choose Azure SQL Database or other appropriate data destination.
      3. Provide connection details like server name, database name, and authentication details.

Step 2: Create a Pipeline

  1. Click on Author in the left panel.
  2. Select + New Pipeline to create a new data pipeline.
  3. Give the pipeline a meaningful name (e.g., BulkInsertPipeline).

Step 3: Add the Copy Activity

  1. In the pipeline, click + Add Activities.
  2. Drag and drop the Copy Data activity into the pipeline canvas.
  3. Click on the Copy Data activity to configure it.

Step 4: Configure the Source

  1. In the Source tab of the Copy Data activity:
    • Click + New to create a new dataset for the source.
    • Choose the correct dataset type (e.g., SQL Server, Azure Blob Storage).
    • Select the linked service created earlier.
    • Configure the dataset (e.g., select the source table or file path).
  2. Define any filters if you want to limit the data being copied.

Step 5: Configure the Sink (Destination)

  1. Go to the Sink tab in the Copy Data activity:
    • Click + New to create a new dataset for the sink.
    • Choose Azure SQL Database or another relevant data store.
    • Select the linked service created for the sink.
    • Specify the destination table where the data will be inserted.

Step 6: Enable Bulk Insert

  1. In the Sink tab, scroll down to the Settings section.
  2. Enable the Use Bulk Insert option by toggling it on.
  3. (Optional) Adjust settings like Batch Size and Timeout:
    • Batch Size: Number of records copied at a time (adjust based on performance).
    • Write Batch Timeout: Time limit for each batch to complete.

Step 7: Optimize Performance (Optional)

  1. Enable Parallel Copies: Go to the Settings tab in the Copy Activity and set Degree of Copy Parallelism to a higher number for faster copying.
  2. Compress Data: Use compression if your source supports it to reduce the amount of data transferred over the network.

Step 8: Debug and Run the Pipeline

  1. Click Debug to test the pipeline before running it live.
  2. Check the Output window to see if the data transfer is successful.
  3. Once everything looks good, click Trigger Now to run the pipeline.
  4. Monitor the progress in the Monitor tab.

Step 9: Monitor and Verify

  1. Go to the Monitor tab to check the status of the pipeline.
  2. Review the logs to ensure the bulk insert completed without errors.
  3. Check the destination (sink) to verify that the data was copied correctly.

Best Practices for Bulk Insert

  1. Use Proper Batch Sizes: Experiment with batch sizes to find the best performance for your scenario.
  2. Monitor Performance: Use ADF’s monitoring tools to track pipeline performance.
  3. Avoid Overloading the Sink: Ensure the destination can handle bulk loads without causing locks or timeouts.

Conclusion

The Bulk Insert Copy Method in Azure Data Factory is a powerful way to move large volumes of data quickly and efficiently. By following the steps outlined above, you can easily set up a pipeline to transfer data using bulk insert. Whether you’re moving data to a data warehouse or syncing large datasets, this method saves time and resources. Give it a try and experience fast, efficient data copying in Azure!

ITECHSTORECA

FOR ALL YOUR TECH SOLUTIONS