Step-by-Step Guide to Creating a Copy Activity Pipeline in Azure Data Factory

Azure Data Factory is a managed cloud service for data integration, automating data movement and transformation. It orchestrates existing services to collect raw data and transform it into actionable insights.

Let’s dive deeper into creating link services, datasets, pipelines, and copy activities in Azure Data Factory. I’ll provide step-by-step instructions and include relevant code snippets.

Prerequisites

Before we begin, ensure you have the following:

• An Azure SQL Database set up with the target table where you want to load data.
• A CSV file (or any other supported format) available in Azure Blob Storage that contains the data you want to copy.

Creating Linked Services

Linked services are essential for connecting your data stores to Azure Data Factory. They act as connection strings, defining how ADF connects to external resources. Here’s how to create them:

Creating an Azure Blob Storage Linked Service

1. In the Azure Data Factory Studio, navigate to the Author tab (pencil icon).
2. Click the plus sign and choose Linked Service.
3. Select Azure Blob Storage as the connector.
4. Configure the service details, including the storage account connection string.
5. Test the connection and create the new linked service.

{
  "name": "AzureBlobStorageLinkedService",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "AzureBlobStorage",
    "typeProperties": {
      "connectionString": "DefaultEndpointsProtocol=https;AccountName=myaccount;AccountKey=mykey;EndpointSuffix=core.windows.net"
    }
  }
}
        

Creating an Azure SQL Database Linked Service

1. Follow similar steps as above but choose Azure SQL Database as the connector.
2. Provide the necessary connection details, including server name, database name, username, and password.

{
  "name": "AzureSqlDatabaseLinkedService",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "connectionString": "Server=myserver.database.windows.net;Database=mydb;User ID=myuser;Password=mypassword;Encrypt=true;Connection Timeout=30;"
    }
  }
}
        

Creating Datasets

Datasets define the structure of your data within linked data stores. Let’s create two datasets: one for Azure Blob Storage and another for Azure SQL Database.

Creating an Azure Blob Storage Dataset

1. In the Data Factory Studio, select the Author tab.
2. Click the plus sign and choose Dataset.
3. Select Azure Blob Storage as the connector.
4. Configure the dataset properties, including the blob container and folder.

{
  "name": "AzureBlobDataset",
  "type": "Microsoft.DataFactory/factories/datasets",
  "properties": {
    "linkedServiceName": {
      "referenceName": "AzureBlobStorageLinkedService",
      "type": "LinkedServiceReference"
    },
    "type": "AzureBlob",
    "typeProperties": {
      "folderPath": "mycontainer/myfolder",
      "format": {
        "type": "TextFormat",
        "columnDelimiter": ","
      }
    }
  }
}
        

Creating an Azure SQL Table Dataset

1. Follow similar steps as above, but choose Azure SQL Table as the connector.
2. Specify the table name and map columns if needed.

{
  "name": "AzureSqlTableDataset",
  "type": "Microsoft.DataFactory/factories/datasets",
  "properties": {
    "linkedServiceName": {
      "referenceName": "AzureSqlDatabaseLinkedService",
      "type": "LinkedServiceReference"
    },
    "type": "AzureSqlTable",
    "typeProperties": {
      "tableName": "MyTable"
    }
  }
}
        

Creating a Pipeline

1. Add a new pipeline in the Data Factory Studio.
2. Drag and drop activities onto the canvas, such as Copy Activity.
3. Configure the source and sink datasets, mapping columns, and any transformations.

Configuring the Copy Activity

1. In the Copy Activity, specify the source (AzureBlobDataset) and sink (AzureSqlTableDataset).
2. Map columns if needed.
3. Set up fault tolerance options (e.g., skip incompatible rows).

{
  "name": "MyCopyPipeline",
  "properties": {
    "activities": [
      {
        "name": "MyCopyActivity",
        "type": "Copy",
        "inputs": [
          {
            "referenceName": "AzureBlobDataset",
            "type": "DatasetReference"
          }
        ],
        "outputs": [
          {
            "referenceName": "AzureSqlTableDataset",
            "type": "DatasetReference"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "BlobSource"
          },
          "sink": {
            "type": "SqlSink"
          },
          "translator": {
            "type": "TabularTranslator",
            "mappings": [
              {
                "source": {
                  "name": "Column1"
                },
        

Running the Pipeline

After creating your Azure Data Factory pipeline, you’ll want to execute it. Here are a few ways to do that:

1. Manual Execution.
2. Scheduled Execution.
3. Event-Driven Execution.

Additional Tips for Effective Pipeline Development:

Save Your Changes and Refresh ADF Before Publishing:

• Always refresh your Azure Data Factory (ADF) before hitting the “Publish” button to avoid overwriting your colleagues’ changes.
• This simple step can prevent unnecessary frustration.

Find Dependencies Before Modifying ADF Components:

• Check related properties to identify who is using your ADF objects (Linked Services, Datasets, Pipelines, Data Flows).
• Understand dependencies before making changes to avoid unexpected issues.

Clone ADF Components for Troubleshooting:

• Clone Datasets, Data Flows, or Pipelines when troubleshooting issues.
• Remove recently changed code and gradually add tasks back to identify the root cause.

Use Annotations for Easy Tracking:

• Annotate your ADF components with notes, rules, expressions, or other relevant information.
• Easily track and trace values used during pipeline execution.

Parameterize Everything:

• Use parameters, variables, and global parameters for reusability.
• Parameterize Linked Services, Datasets, and Data Flows.
• Simplify maintenance and make your pipelines adaptable.

Final Thoughts

Azure Data Factory is a powerful tool for data engineering. As your database grows, ADF can help manage complex data movement scenarios. With features like Azure Functions integration, the possibilities are endless. Even connecting to non-Microsoft databases is feasible! Remember to explore the official documentation and community resources to continue learning and optimizing your data pipelines.

Happy data engineering! ????

!Azure Data Factory

For more insights and community discussions, check out the Azure Data Factory Blog.