Parameterization In Azure Information Manufacturing unit Linked Providers

Introduction

The linked providers within the azure information manufacturing unit have the choice to parameterize and go dynamic values at run time. There may be necessities the place we need to join totally different databases from the identical logical server or totally different database servers themselves. Historically we might create separate linked providers for every database or database server however with the parameterization characteristic, we are able to create new parameters and add them dynamically which may then be chosen when creating the linked providers as a substitute of offering all the small print whereas creating it. We’ll see the way to create this within the following steps…

Create Parameterized linked providers

We’ll verify by creating a brand new linked service. After clicking the brand new linked service, choose azure managed occasion however that is purely the consumer’s selection, you may choose any supply that you just wish to join with.

I’ve supplied the database occasion identify immediately and I need to experiment with the parameterization in database names as I don’t have a single DB within the occasion.

So as to add parameters simply click on on the database identify area and you may see there’s an possibility so as to add dynamic variables known as ‘Add dynamic content material. There’s a + signal seen beneath by which you’ll be able to add new parameters which is without doubt one of the strategies, however we’re going to create in one other manner.

I’ve added the ‘dbName’ parameter itemizing. You may add any variety of parameters by clicking the New button as per your requirement. As soon as it’s created you may then be capable of add it to the necessary ‘Database Identify’ area

The curly brackets current subsequent to the linked service will enable you to see by the JSON code of this newly created linked service. The connection string will comprise the connection particulars together with the database identify as identical as you’ve gotten entered, i.e., as ‘dbname’ parameter.

JSON

{
    "identify": "AzureSqlManagedInst",
    "sort": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "parameters": {
            "dbName": {
                "sort": "string"
            }
        },
        "annotations": [],
        "sort": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "built-in safety=False;encrypt=True;connection timeout=30;information supply=azsqlinst.database.home windows.internet;preliminary catalog=@{linkedService().dbName};consumer id=xxxxx",
            "encryptedCredential": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
        }
    }
}

At any time when we create a dataset for this linked service, we are able to go the worth of the parameter on the runtime as a substitute of offering them on the creation. We will make use of the linked service by utilizing it for all of the databases which are current in a database server. I do know it’s getting a bit complicated for now, however you’ll perceive extra on this clearly within the upcoming articles the place we are going to be taught extra in regards to the stream of passing the worth of the parameter from the Linkedservices à dataset and dataset à pipeline.

The good characteristic about parametrization is that it isn’t restricted to dbname or any single parameter, we are able to add as many parameters we’d like like server identify, username, password, and many others. By including the connection particulars as parameters, we are able to create a single generic linked service for all databases or servers and go the values on the run-time. It helps to handle the linked providers effectively and keep away from creating a number of linked providers for every undertaking.

Supported linked service varieties

As of this writing, the next are the supported information shops for linked providers creation within the Azure information manufacturing unit. The record will continue to grow sooner or later as we hope that Microsoft will carry on growing the supported datastores.

• Amazon Redshift
• Amazon S3
• Amazon S3 Appropriate Storage
• Azure Blob Storage
• Azure Cosmos DB (SQL API)
• Azure Information Lake Storage Gen2
• Azure Database for MySQL
• Azure Databricks
• Azure Key Vault
• Azure SQL Database
• Azure SQL Managed Occasion
• Azure Synapse Analytics
• Azure Desk Storage
• Generic HTTP
• Generic REST
• MySQL
• Oracle
• Oracle Cloud Storage
• SQL Server

Abstract

On this article, we noticed what Parameterized Linked providers in Azure Information Manufacturing unit is. Within the developing articles, we are going to create sensible workflow pipelines and the way parameterization helps in creating and simplifying linked providers.

References

Microsoft official documentation.