Parameterize Linked Services in ADF

by Jul 9, 2020

Parameterize Linked Services 

For my Azure Data Factory solution I wanted to Parameterize properties in my Linked Services. Not all properties are Parameterized by default through the UI. But there’s another way to achieve this.

 

 

Linked Service Azure Data Factory

Linked Service

Open your existing Linked Services.

Linked Services ADF

In this situation I want to Parameterize my FTP connection so that I can change the Host name based on a Azure Key Vault Secret.

By default is this not possible through the UI but in the Bottom of your Linked Service there is a Advanced box

Linked Services Advanced

 

If you enable this box you can start building your own connection, but also create your own Parameters for this connection.

How to start:

As a base we will use the default code or our connection

Linked Services Code

{
    "name": "LS_FTP_SOURCE",
    "properties": {
        "annotations": [
            "stage: none",
            "scenario: demo",
            "environments: dvlm"
        ],
        "type": "FtpServer",
        "typeProperties": {
            "host": "ftp.erwindekreuk.com",
            "port": 21,
            "enableSsl": true,
            "enableServerCertificateValidation": true,
            "authenticationType": "Basic",
            "userName": "ftp_down",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_AKV_OXGN",
                    "type": "LinkedServiceReference"
                },
                "secretName": "secretname"
            }
        }
    },
    "type": "Microsoft.DataFactory/factories/linkedservices"
}
Copy this code to your advanced box and enable the option Specify dynamic contents in JSON format/

Now you can start adding new parameters.

Linked Services Advanced

If you want to Parameterize your HOST name  connection you have to add in the top of the code a new Parameter, under the type of your connection

    "properties": {
        "type": "FtpServer",
        "parameters": {
            "ConnectionKeyvaultSecret": {
                "type": "string"
            }

After you have done this, you need to specify for which properties you want to use this parameter. In my case I want to read the parameter form my Azure Key Vault for my HOST propertie.

The JSON code below will now use above parameter as an input.

        "typeProperties": {
            "host": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_AKV_OXGN",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().ConnectionKeyvaultSecret",
                    "type": "Expression"
                }
            }

Save your connection and you will see that your UI is changed and that you have to define all your setting through the Advanced Editor.

If you test your connection you will now see that you have to fill in a parameter.

Linked Service Test Connection

And now you can create parameters of every TypeProperties within your connection.

The code below will create Parameters for your Host, Username and Password entries with Azure Key Vault enabled. For the authenticationType you have to choose between Basic and Anonymous. But can also at this to your Azure Key Vault.

{
    "name": "LS_FTP_SOURCE",
    "properties": {
        "type": "FtpServer",
        "parameters": {
            "ConnectionKeyvaultSecret": {
                "type": "string"
            },
            "UsernameKeyvaultSecret": {
                "type": "string"
            },
            "PasswordKeyvaultSecret": {
                "type": "string"
            },
            "authenticationType": {
                "type": "string"
            }
        },
        "annotations": [ ],
        "typeProperties": {
            "host": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_AKV_OXGN",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().ConnectionKeyvaultSecret",
                    "type": "Expression"
                }
            },
            "port": 21,
            "enableSsl": false,
            "enableServerCertificateValidation": false,
            "authenticationType": "@linkedService().authenticationType",
            "userName": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_AKV_OXGN",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().UsernameKeyvaultSecret",
                    "type": "Expression"
                }
            },
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_AKV_OXGN",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().PasswordKeyvaultSecret",
                    "type": "Expression"
                }
            }
        }
    }
}

Thanks for reading my blog post and have fun with Parameterization of your Linked Services in ADF.

Feel free to leave a comment

2 Comments

  1. David Laplante

    Thanks for the article! Just a note, proper indentation of the json goes a long way in helping everyone understand the logic 🙂

    Reply
    • Erwin

      Thanks for the Feedback David, looks like my editor removed the indentation. Going to look if I can better integrate/visualize this within my webpage.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

nine − 8 =

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

ADF: Get Metadata Activity stopped working

Meta Data ActivityToday my pipelines in Azure Data Factory (ADF) suddenly stopped working. The output structure was not found. Quit strange while these pipelines have been running for weeks.    Invalid Template After debugging my Pipeline I found out the...

Are you using Azure DevOps?

Azure DevOpsMore and more users are starting with Azure DevOps. Azure DevOps can be used for up to 5 users free of charge and is therefore a great start to start a project.    Build and Release I will not describe what Azure DevOps can do, because that is too much to...

Restore a accidentally deleted Azure SQL Database

Help I deleted my Logical Server​OOPS Have you ever experienced that you accidentally deleted your Logical Server in Azure?  Because, for example, you made your Pipeline wrong. Surely. And of course you didn't have a backup in your storage either.Well I must confess...

Goodbye 2020 Hello 2021

Goodbye 2020 Started to work for InSpark Last year was certainly an eventful year. Started with a new job at InSpark and after 10 weeks we all know what happened, the first intelligent lockdown. The Netherlands was partially locked, but our office was immediately...

Rerun Pipeline activities in Azure Data Factory

Rerun Pipeline activities in ADF! As of today you can rerun or partially, yes you’re reading it correct partially, rerun you Azure Data Factory pipeline.Where you previously had to run the entire Pipeline again, you can now run a part of the Pipeline. This can save a...

Calculate Workingdays including Holidays with T-SQL

Calculate Workingdays between 2 Date columnsRecently I have been getting some questions from my customers, can I calculate the number of workdays between 2 dates? Of course my answer was, yes you can. But I do want certain closing dates and holidays of our company not...

Azure Synapse Analytics

Azure Synapse Analytics  Insights for all Azure Synapse provides a breathtaking view of your data across data warehouses and big data analytics systems. Bringing these two worlds together into a single service is challenging as it requires unifying similar...

Book of News Microsoft Ignite 2019

Microsoft Ignite 2019MSIgnite 2019 has almost come to an end. A lot of new Annoucement have been released and presented.  Would you also like to know which announcements have been made?Microsoft has released during MSIgnite a "Book of News". This book contains all new...

Azure Data Factory: Save a pipeline as a Template

Saving a PipelineAnother great improvement in Azure Data Factory. Saving you template!How can you save your template? First of all you need to connect your Azure Data Factory to  a GIT integration. Both Azure DevOps GIT and GitHub are supported. Please follow this...

Proud to be an ADF Hackathon winner!

Azure Data Factory HackathonAround the clock During the event 'Around the clock Azure SQL and Azure Data Factory' event on Feb 3, 2021, they kicked-off the Azure Data Factory (ADF) Hackathon. Recording of this event can be found here.Winner I submitted a ADF Pipeline...