How To Use ‘Specify dynamic contents in JSON format’ in Azure Data Factory Linked Services

Posted on by mrpaulandrew

Make any Azure Data Factory Linked Service dynamic!

In a few different community circles I’ve been asked ‘how to handle dynamic Linked Service connections in Azure Data Factory if the UI doesn’t naturally support the addition of parameters’. Depending on the Linked Service the support for this varies.

Well, the answer, or should I say, Microsoft’s quick hack answer, is use the ‘Specify dynamic contents in JSON format’ option available for all Linked Services regardless of there type/popularity. This feature enables an empty text box where you can define anything about the Linked Service. It also removes all other attributes from the Linked Service connection panel, except Title and Description.

What this setting does is basically allow you to write you own JSON definition (in full) for the intended Linked Service. This then gives you the flexibility to parameterise anything in the key/value pair structure and means you don’t need to worry about Data Factory naturally supporting the Linked Service being dynamic via the usual developer canvas and expression builder. The down side that I’ve already alluded to, is that if you only want one value in the Linked Service to be dynamic you’ll have to provide the complete JSON definition for everything.

To be explicit about this behaviour if you haven’t seen it before…

If you want to inject parameters into a Linked Service for an Azure SQLDB, you get the full UI and expression builder support, as follows:

I blogged about doing this in more detail for an Azure SQLDB here if you want to look back.

However, in the equivalent panel for an Azure Key Vault Linked Service connection you get nothing, no expression builder support.

That is, unless you expand the Advanced section and tick Specify dynamic contents in JSON format. Then the UI panel changes to the following.

What we can then do is add the complete JSON definition for the Linked Service including any required parameters.

Let’s say, for example, we want the Key Vault URL to be dynamic, you could add the JSON like this:

Then, like other Data Factory components, the parameter value would bubble up wherever you try to use the dynamic content. In the case of the Key Vault Linked Service, even when you hit Test Connection.

Give it a try!

Dynamic values for all your Linked Services using custom JSON definitions via the ‘Specify dynamic contents in JSON format’ tick box  🙂

Hope this helps.

Many thanks for reading.

16 thoughts on “How To Use ‘Specify dynamic contents in JSON format’ in Azure Data Factory Linked Services”

  1. Pingback: How To Use ‘Specify dynamic contents in JSON format’ in Azure Data Factory Linked Services – Sudhakar's blog
  2. Pingback: Best Practices for Implementing Azure Data Factory – Welcome to the Technical Community Blog of Paul Andrew

  3. An interesting discussion is value comment. I feel that you should write extra on this topic, it may not be a taboo topic however usually individuals are not sufficient to speak on such topics. To the next. Cheers

    Like

    Reply

  5. you can further use dynamic JSON to hack KeyVault references into almost everywhere
    This also comes in very handy if you want to manage all environment-specific settings using a KeyVault and during the deployment you simply change the baseURL of the KeyVault linked service then

    Liked by 1 person

    Reply

  6. Hi, Paul!
    Thank you for your tutorial here. I am basically doing the exact same thing, but I’m having issues when I run the pipeline. I suspect that the linked service does not “bubble up” to the AKV linked service connection on runtime. ADF simply says “{“code”:”BadRequest”,”message”:”No value provided for Parameter ‘baseUrl'”,”target”:”pipeline/****/runid/hash”,”details”:null,”error”:null}” and won’t start. “Test connection” is green on every step of the way. How can I make sure that the linked service property is passed along with the linked service reference?

    Like

    Reply

      1. Thank you for your reply!

        I’m actually not trying to make the referenceName dynamic, if that’s what you mean. Much like what you have here, I am using referenceName “GenericKeys”, but I want the baseUrl in the now parameterized connection to change depending on which environment I’m in. The dataset has a parameter “env” which changes.

        So in the window pane “Edit linked service (Azure SQL Database)”, my JSON looks like this:

        {
        “name”: “linked_service_auto_sql”,
        “type”: “Microsoft.DataFactory/factories/linkedservices”,
        “properties”: {
        “parameters”: {
        “ServerName”: {
        “type”: “String”,
        “defaultValue”: “**t”
        },
        “DBName”: {
        “type”: “String”,
        “defaultValue”: “**”
        },
        “SecretName”: {
        “type”: “String”,
        “defaultValue”: “**”
        },
        “baseUrl”: {
        “type”: “string”,
        “defaultValue”: “**”
        }
        },
        “annotations”: [],
        “type”: “AzureSqlDatabase”,
        “typeProperties”: {
        “connectionString”: “Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=@{linkedService().ServerName};Initial Catalog=@{linkedService().DBName};User ID=**”,
        “password”: {
        “type”: “AzureKeyVaultSecret”,
        “store”: {
        “referenceName”: “GenericKeys”,
        “type”: “LinkedServiceReference”
        },
        “secretName”: {
        “value”: “@linkedService().SecretName”,
        “type”: “Expression”
        }
        }
        }
        }
        }

        and my suspicion is that the GenericKeys connection never gets the baseUrl. I’ve tried using both baseUrl (also calling it vaultBaseUrl) under the level of typeProperties, password, and store, but no luck. I’ve drained Google for relevant cases now, and I can’t seem to find any indication of how to make the url dynamic this way.

        The snippet I tried putting in the different spots:
        “baseUrl”:{
        “value”: “@linkedService().baseUrl”,
        “type”: “Expression”
        }

        Any ideas?

        Like

      2. Hey, I see, yes, this is a common problem that I typically have to handle at deployment time with multiple data factory’s. As the Key Vault baseURL is part of the second level of linked services you can’t surface it as part of datasets and pipelines.

        Like

  7. Thanks for the article Paul, I’m trying to make the linked service for an Azure Data Explorer (Kusto) database parameter dynamic. I took the json from source control for the linked service I’m trying to change and replaced the UI with the json. I added this under parameters:

    ,
    “parameters”: {
    “database”: {
    “type”: “String”
    }
    }

    But the database didn’t become a property I can set in the dataset or the pipeline. Would you know if I got the json wrong?

    Like

    Reply
  8. Pingback: Azure Data Factory, dynamic JSON and Key Vault references | Gerhard Brueckl on BI & Data

  9. Hi There, I wonder if you can help me, We are trying to connect to a 3rd party Azure SQL DB that uses AD Authentication.
    Can I use the JSON option here to do that, as I can select it under the Authentication type?

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.