Azure Data Factory – How to Parameterize Linked Service

The ADF linked Services are the connectors, between source and sink data stores, which are used to move data using pipeline activities. In real time scenario, we need to deal with different databases, blob storage containers, KeyVault secrets in various environments like development, QA, UAT etc. This article describes a general approach to overcome this problem but same concept applies for all the linked services in ADF.

Let’s suppose you have to parameterize database connection string with different server name, database name, username and password. In this article, we will have a SQL Server with multiple database but we have to connect particular only a particular database with a specific user.

Steps to Perform

1. Create a resource group <ADF-Parameterize-Demo>
2. Spin a SQL server instance with a database <My-Database>
3. Create a Key Vault with a database password secret <My-Database-Password>
4. An Azure Data Factory instance

*Steps to create these resources are not covered in this article.

Implementation

Go to Resource Group > Azure Data Factory > Author & Monitor and wait for Azure data factory to open.

Create SQL Service Linked Service : Go Manage> Linked services > New > Azure SQL Database > Advanced > Check “option Specify dynamic contents in JSON format” and paste below JSON

{
    "name": "SQLServerLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "parameters": {
            "DatabaseServerName": {
                "type": "String",
                "defaultValue": "adf-db-server.database.windows.net"
            },
            "DatabaseName": {
                "type": "String",
                "defaultValue": "adf-db"
            },
            "UserName": {
                "type": "String",
                "defaultValue": "adf-db-admin"
            },
            "Password": {
                "type": "String",
                "defaultValue": "Your Password"
            }
        },
        "annotations": [],
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=@{linkedService().DatabaseServerName};Initial Catalog=@{linkedService().DatabaseName};User ID=@{linkedService().UserName};password=@{linkedService().Password}"
        }
    }
}

This JSON defines a dynamic linked service for Azure SQL Server database which takes server name, database name, username and password. But, this point we would not know that linked service would be able to connect data source or not.

To use this linked service first we need to check its connectivity with data source and to do that – Go Manage> Linked services > Click SQLServerLinkedService. The SQL Server linked service screen will show four parameters to pass as input. To test connection, pass all the four parameters, click apply and ok on subsequent screen and connection would be successful if everything fine.

Note : You may need to allow Data Factory Server IP address in the Firewall Settings of Azure SQL Server to test connection.

So, we can see by passing the parameters in your linked service, linked service is able to connect to the database. These values can be passed as pipeline parameters by defining variables and set variables in linked services while creating datasets using linked service.

Important – This concept applies to achieve dynamic connectivity with any linked services. If any linked service does not support Dynamic Content feature in Azure Data Factory then parameters in JSON will allow to pass parameters at runtime.

Hope, this gives an understanding how to Parameterize linked services in Azure Data factory.

If you have any suggestions/feedback, please put them in the comment box.

Happy Learning 🙂