How to Hack ADF for Dynamic Linked Services

Turner Kunkel   •   12.16.2019

Overview

This article will demonstrate how to utilize the JSON editor in Azure Data Factory to provide dynamic linked services in ODBC and File System Linked Service connections.

The examples will provide a guide to help with metadata-driven ADF architectures.

 

Introduction

Azure Data Factory utilizes Linked Services to drive Dataset connections. Linked Services can make many connections within Azure Data Factory, providing a robust orchestration platform for copying and transforming data between systems.

In a straightforward implementation, these connections would be hard-coded. But, in most enterprise applications, we would want to see these connections be dynamic to facilitate transfer from as many sources as possible using the same Linked Service. Microsoft has provided a way for the On-Premises SQL Server to be dynamic. It can take in a set of parameters using the Azure Data Factory UI in order to change its connection to different servers without creating separate Linked Service entries and Dataset entries.

dynamic linked services

You can see the ‘Add Dynamic content’ options here

 

But what if we wanted to dynamically set a connection for a File System connection or an ODBC connection?

We currently do not have UI options for this.

 

 

 

dynamic linked services

File System entries do not have the dynamic option

dynamic linked services

ODBC entries do not have the dynamic option

We can take the following steps to add in parameters to dynamically change the connections for these two connection types.

 

 

 

 

 

 

File System Linked Service

First, we must check on the option to allow us to program some JSON code for the Linked Service.

dynamic linked services

Doing this will take out all UI options on the Linked Service and provide a blank slate for entering dynamic pieces of the connection.

The idea for coding this will be keeping in mind what pieces will be dynamic.

Here is the code to set up a dynamic Host, User, and Password (via Azure Key Vault), driven by the idea that a pipeline will have parameters set when called, then passed to this Linked Service.

{

“properties”: {

“type”: “FileServer”,

“typeProperties”: {

“host”: {

“value”: “@{pipeline().parameters.Directory}”,

“type”: “Expression”

            },

“userId”: {

“value”: “@{pipeline().parameters.User}”,

“type”: “Expression”

            },

“password”: {

“type”: “AzureKeyVaultSecret”,

“store”: {

“referenceName”: “Your_KeyVault_Service”,

“type”: “LinkedServiceReference”

                },

“secretName”: {

“value”: “@{pipeline().parameters.Secret}”,

“type”: “Expression”

                }

            }

        },

“connectVia”: {

“referenceName”: “Your_Integration_Runtime”,

“type”: “IntegrationRuntimeReference”

        },

“annotations”: []

    }

}

dynamic linked servicesAfter this is set, we can set up a ‘child’ pipeline with parameter names matching those in the JSON code.

With metadata set using a Lookup task, a ‘parent’ pipeline with a For-Each loop can take in a set of parameter values and call the ‘child’ pipeline using a Dataset connected to the new dynamic Linked Service.

Each call to the ‘child’ pipeline within the For-Each loop will then connect to its respective File System locations as specified by the metadata.

Example metadata:

  • \\Location1\FilesToUse\, User1, Secret1
  • \\Location2\FilesToUse\, User2, Secret2

The For-Each loop would call two instances of the pipeline and input the values from the metadata, and then accomplish whatever we’d want the pipeline to do with the files in these locations.

dynamic linked services

Parent pipeline

dynamic linked services

For-Each loop settings

dynamic linked services

For-Each calling ‘child’ pipeline

 

 

 

 

ODBC Linked Service

Again, we are going to click on the option for us to edit the JSON for the Linked Service.

The ODBC Linked Service has different options and configurations than the File System Linked Service of course, so our code will act a bit differently.

Here we set parameters for Connection String (via Azure Key Vault), User, and Password (via Azure Key Vault).

Keep in mind the Connection String should not include the User and Password, just a general connection to whatever ODBC server we are looking for.

{

“properties”: {

“type”: “Odbc”,

“parameters”: {

“Secret”: {

“type”: “String”,

“defaultValue”: “Default_Value”

            },

“Password”: {

“type”: “String”,

“defaultValue”: “Default_Value”

            },

“User”: {

“type”: “String”,

“defaultValue”: “Default_Value”

            }

        },

“annotations”: [],

“typeProperties”: {

“connectionString”: {

“type”: “AzureKeyVaultSecret”,

“store”: {

“referenceName”: “Your_KeyVault_Service”,

“type”: “LinkedServiceReference”

                },

“secretName”: {

“value”: “@{linkedService().Secret}”,

“type”: “Expression”

                }

            },

“authenticationType”: “Basic”,

“userName”: {

“value”: “@{linkedService().User}”,

“type”: “Expression”

            },

“password”: {

“type”: “AzureKeyVaultSecret”,

“store”: {

“referenceName”: “Your_KeyVault_Service”,

“type”: “LinkedServiceReference”

                },

“secretName”: {

“value”: “@{linkedService().Password}”,

“type”: “Expression”

                }

            }

        },

“connectVia”: {

“referenceName”: “Your_Integration_Runtime”,

“type”: “IntegrationRuntimeReference”

        }

    }

}

Publish/save the changes and re-load Azure Data Factory to see the parameters appear in the Linked Service when connecting a Dataset, in which we can add dynamic entries to.

dynamic linked services

Now we can set up a Lookup and For-Each loop to call this Dataset and pass different connections to apply the same operations to, just like we would a dynamic SQL Server connection.

 

 

 

 

 

 

dynamic linked services

Pipeline

dynamic linked services

For-Each activity settings

Now, anything within the For-Each loop that references the ODBC Dataset will dynamically pass in Secret, Password, and User from metadata and perform whatever activities needed.

Example metadata:

  • Connection_Secret1, User1, PasswordSecret1
  • Connection_Secret2, User2, PasswordSecret2

This would perform activities using these connections in parallel in the For-Each loop.

 

In Summary

Azure Data Factory in its current state doesn’t allow for a lot of parameters for Linked Services when we’d like to connect to certain sources dynamically.

We have shown here that, for at least two connection types, we can add JSON code to help us add parameters for our connections.

Of course, this is not limited to the specific way I’ve shown, nor to the connections I’ve shown.

The examples are to give you ideas on how to reduce the number of pipelines compared to not using dynamic connections.

Let me know if you have any questions at turner.kunkel@talavant.com, and thank you for reading.


Turner Kunkel

Architect

Share This Article


At Talavant, our goal is to help you discover, ask and answer the right questions – so you get more value from your data to drive your business forward. You’ll gain a competitive edge with faster, more relevant analytics. It’s all part of our focused, holistic approach that goes beyond tools.

Ready to get started?

Contact Us