Azure Data Factory: Making Non-Dynamic Linked Services Dynamic

Linked Service Options Using the UI

Note: The example here is the Salesforce linked service, but this technique also works for other linked services where the UI does not support adding parameterised properties.

One of my clients has been adding data from multiple Salesforce instances to their data platform this week. One of their developers asked me if the Salesforce linked service could be made dynamic, as there is no place in the GUI to add parameters, or a dynamic values for the URL, user name or credentials.

Creating a Salesforce Linked Service vs a Data Lake Gen 2 Linked Service. Notice the Data lake one has an option for parameters and the Secret Name can be set to a dynamic expression.

Accessing Hidden Setting via JSON

However, this is not the full story. If we create the salesforce connector with the bare minimum filled in, we can inspect the JSON. In the list of linked services, click the curly braces button that appears as you hover on the linked service you added.

This brings up the JSON view, where we can edit the JSON directly. The JSON should look something like this.

{
    "name": "LS\_Salesforce",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": \[\],
        "type": "Salesforce",
        "typeProperties": {
            "environmentUrl": "https://login.salesforce.com",
            "username": "user",
            "encryptedCredential": "ew0KICAiVmVyc2lvbiI6ICIyMDE3LTExLTMwIiwNCiAgIlByb3RlY3Rpb25Nb2RlIjogIktleSIsDQogICJTZWNyZXRDb250ZW50VHlwZSI6ICJQbGFpbnRleHQiLA0KICAiQ3JlZGVudGlhbElkIjogIkNPLVVLLUFERkVHUkVTU18yYTZhNjE0NC0xODkzLTRiOTEtODY3NC03NjBjNjc0NGRkYjEiDQp9"
        }
    }
}

So how does that help us? Well the a Data Lake Gen 2 Linked Service with a couple of parameters set might look something like this.

{
    "name": "LS\_AzureDataLakeStorage",
    "properties": {
        "parameters": {
            "Url": {
                "type": "string"
            },
            "SecretName": {
                "type": "string"
            }
        },
        "annotations": \[\],
        "type": "AzureBlobFS",
        "typeProperties": {
            "url": "@{linkedService().Url}",
            "accountKey": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS\_KeyVault",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().SecretName",
                    "type": "Expression"
                }
            }
        }
    }
}

We can see the JSON for adding the parameters is pretty simple, and adding an expression to a value is simple too. In fact the only valid expressions here are simple parameter references, no complex expressions to debug!

So what happens if we modify the JSON for the Salesforce Linked Service to add some parameters, and set the properties using them. We can modify the JSON directly, and when we click apply, it should work. Here is the JSON for a fully parameterised Salesforce Linked Service.

{
    "name": "LS\_Salesforce",
    "properties": {
        "annotations": \[\],
        "type": "Salesforce",
        "parameters": {
            "url": {
                "type": "string"
            },
            "username": {
                "type": "string"
            },
            "passwordSecretName": {
                "type": "string"
            },
            "securityTokenSecretName": {
                "type": "string"
            }
        },
        "typeProperties": {
            "environmentUrl": {
                "value": "@linkedService().url",
                "type": "Expression"
            },
            "username": {
                "value": "@linkedService().username",
                "type": "Expression"
            },
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS\_KeyVault",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().passwordSecretName",
                    "type": "Expression"
                }
            },
            "securityToken": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "costaindlakv",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().securityTokenSecretName",
                    "type": "Expression"
                }
            }
        }
    }
}

So how does this look if we click on the linked service to view the details. Well, ADF does not magically create a more useful UI for us. Instead it just shows the JSON we edited in the advanced dropdown area. No more pretty UI at all!

Testing the Customised Linked Service

And if we want to test the connection? Well it prompts us for the parameters, just like any other parameterised linked service would.

You can also add default values to the parameters in the JSON, but I find that confuses more than it helps when troubleshooting.

We can add a Salesforce dataset at this point, and it will allow us to pass the parameters to the linked service just like we can with datasets using Data Lake Gen 2, Azure SQL DB, etc. We can add parameters to the dataset too, so that the pipeline can drive the connection we are making, and the linked service and dataset are generic and reusable.

Does this Work Far All Linked Service Types?

This tip can also be used on other linked services that don’t look like they can be parameterised from the GUI. It just takes a little knowledge of the JSON used to build the objects.

I’ve been using this with various clients for the past 2 years, so it’s not a hacky workaround, rather it seems that the UI for some connectors are not as polished as others.

• ← Previous Post
• Next Post →