Parameterization In Azure Data Factory Linked Services

Introduction

The linked services in the azure data factory have the option to parameterize and pass dynamic values at run time. There might be requirements where we want to connect different databases from the same logical server or different database servers themselves. Traditionally we would create separate linked services for each database or database server but with the parameterization feature, we can create new parameters and add them dynamically which can then be selected when creating the linked services instead of providing all the details while creating it. We will see how to create this in the following steps…

Create Parameterized linked services

We will check by creating a new linked service. After clicking the new linked service, select azure managed instance but this is purely the user’s choice, you can select any source that you would like to connect with.

I have provided the database instance name straight away and I want to experiment with the parameterization in database names as I don’t have a single DB in the instance.

To add parameters just click on the database name field and you can see there is an option to add dynamic variables called ‘Add dynamic content. There is a + sign visible below through which you can add new parameters which is one of the methods, but we are going to create in another way.

I have added the ‘dbName’ parameter listing. You can add any number of parameters by clicking the New button as per your requirement. Once it is created you can then be able to add it to the mandatory ‘Database Name’ field

The curly brackets present next to the linked service will help you to see through the JSON code of this newly created linked service. The connection string will contain the connection details including the database name as same as you have entered, i.e., as ‘dbname’ parameter.

JSON

{
    "name": "AzureSqlManagedInst",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "parameters": {
            "dbName": {
                "type": "string"
            }
        },
        "annotations": [],
        "type": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "integrated security=False;encrypt=True;connection timeout=30;data source=azsqlinst.database.windows.net;initial catalog=@{linkedService().dbName};user id=xxxxx",
            "encryptedCredential": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
        }
    }
}

Whenever we create a dataset for this linked service, we can pass the value of the parameter at the runtime instead of providing them at the creation. We can make use of the linked service by using it for all the databases that are present in a database server. I know it’s getting a bit confusing for now, but you will understand more on this clearly in the upcoming articles where we will learn more about the flow of passing the value of the parameter from the Linkedservices à dataset and dataset à pipeline.

The coolest feature about parametrization is that it is not limited to dbname or any single parameter, we can add as many parameters we need like server name, username, password, etc. By adding the connection details as parameters, we can create a single generic linked service for all databases or servers and pass the values at the run-time. It helps to manage the linked services efficiently and avoid creating multiple linked services for each project.

Supported linked service types

As of this writing, the following are the supported data stores for linked services creation in the Azure data factory. The list will keep growing in the future as we hope that Microsoft will keep on increasing the supported datastores.

• Amazon Redshift
• Amazon S3
• Amazon S3 Compatible Storage
• Azure Blob Storage
• Azure Cosmos DB (SQL API)
• Azure Data Lake Storage Gen2
• Azure Database for MySQL
• Azure Databricks
• Azure Key Vault
• Azure SQL Database
• Azure SQL Managed Instance
• Azure Synapse Analytics
• Azure Table Storage
• Generic HTTP
• Generic REST
• MySQL
• Oracle
• Oracle Cloud Storage
• SQL Server

Summary

In this article, we saw what Parameterized Linked services in Azure Data Factory is. In the coming up articles, we will create practical workflow pipelines and how parameterization helps in creating and simplifying linked services.

References

Microsoft official documentation.