Azure Data Factory (ADFv2) - Parameterizing Linked Services That Do Not Have Built-In Dynamic Content

11/7/2019

There are several linked service types that do not have built-in dynamic content in which it is possible to reference a parameter. For example, if your linked service is an Azure SQL Database, you can parameterize the server name, database name, user name, and Azure Key Vault secret name. This allows for one linked service for all Azure SQL Databases. However, if your linked service is HTTP or SFTP (or many others), there is no "dynamic content" option for key properties. This forces one linked service and one dataset for every HTTP Base URL or one linked service and dataset for every SFTP host, port and user name.

Example HTTP linked service with no built-in parameters and therefore no dynamic content.

Example Azure SQL Database linked service with nicely parameterized properties.

Microsoft has provided a nice solution to this problem, and it lies in the "Advanced" section of each linked service. Understanding that it is JSON syntax that sits behind the GUI of linked services, to add parameters to a linked service, we can manually modify the JSON. At this point and if this blog post were a Microsoft Doc, most of us would be scrolling down and saying, "Just show me the JSON!". Knowing this, here you go -->

Default / original hard-coded linked service JSON
{
"name": "CWS_ADF_API",
"type": "Microsoft.DataFactory/factories/linkedservices",
"properties": {
"annotations": [],
"type": "HttpServer",
"typeProperties": {
"url": "https://login.microsoftonline.com",
"enableServerCertificateValidation": true,
"authenticationType": "Anonymous"
}
}
}

(Ctrl + Mouse Wheel Scroll to enlarge Weebly blog images)

***************************************************************************************************************************

Parameterized linked service JSON
{
"name": "CWS_API_AutoResolveRuntime",
"type": "Microsoft.DataFactory/factories/linkedservices",
"properties": {
"type": "HttpServer",
"parameters": {
"BaseURL": {
"type": "string",
"defaultValue": "https://login.microsoftonline.com"
}
},
"annotations": [],
"typeProperties": {
"url": "@linkedService().BaseURL",
"enableServerCertificateValidation": true,
"authenticationType": "Anonymous"
}
}
}

(Ctrl + Mouse Wheel Scroll to enlarge Weebly blog images)

Now the question is, "How does the dataset handle this customized linked service?" I was very pleased to find that the integration of the custom JSON into a dataset was seamless.

Example dataset based on a non-parameterized linked service. (As the linked service property is not parameterizable in datasets, this also forces a duplicate dataset for every HTTP linked service.)

The same dataset now refreshed to use the customized linked service is pictured below. As with any dataset that is based on a parameterized linked service, dataset parameters have to be created to receive the value from a pipeline and then pass the value to the linked service. What is fabulous is that there is no need to manually modify the JSON behind the dataset -- it recognizes the new linked service parameter automatically.

One of the nice things about Azure Data Factory (ADFv2) is the level of parameterization now available. Until all linked service properties accept dynamic content by default, this is a nice workaround.

6 Comments

Serge

11/8/2019 08:24:28 pm

Hi Delora,

thank you for the great info. I've managed to do the same with the FileServer linked service.

Unfortunately, the factory does not expose the custom parameters into the data set. I had to manually edit the dataset JSON with the following code

```
"linkedServiceName": {
"referenceName": "myFileServerLinkedService",
"type": "LinkedServiceReference",
"parameters": {
"host": "@dataset().host",
"userId": "@dataset().userId",
"secretName": "@dataset().secretName"
}
},
```

Now it's working fine, allows to dynamically connect to different hosts. But, the Factory might delete this custom code if you save it again from UI.

According to the reference, the parameters section should be available for any dataset types...
https://docs.microsoft.com/en-us/rest/api/datafactory/datasets/createorupdate#linkedservicereference

Delora Bradish link

11/11/2019 08:48:26 pm

Thank you, Serge, for the follow-up. If the ADF UI doesn't overwrite your custom code in the dataset I'll be surprised as some other testing I was doing with customized dataset JSON did get lost. Best wishes on that, and kudos on the creative alternative.

Serge

11/12/2019 01:19:48 pm

Yes, UI overwrites the custom JSON code. But, there's a trick that Microsoft might close soon with one of the updates - you can modify the JSON directly in GIT repository and then UI will use it as it's own. But, if you save the modified object from UI it will replace the custom code with it's own...

Thank you for the great blog you're running! Very impressive

Prashant Sinha link

2/15/2020 05:13:40 pm

Here's the JSON for Amazon S3 - I agree with Serge, Dataset UI doesn't show the Linked Service parameters sometimes. You can modify the JSON code and git the JSON. I recommend using KVS for the keys, I was just testing it out to build a dynamic ADF pipeline for S3 -- > Azure Data Lake Gen2.

{
"properties": {
"type": "AmazonS3",
"parameters": {
"accessKey": {
"type": "string",
"defaultValue": ""
},
"secretKey": {
"type": "string",
"defaultValue": ""
}
},
"annotations": [],
"typeProperties": {
"accessKeyId": "@linkedService().accessKey",
"secretAccessKey": "@linkedService().secretKey"
}
}
}

Talib

3/25/2020 01:51:27 am

Thanks for the article.
I am struggling to get my datalake gen2 linked service running with parameters. I have to parameterise it to reffer both dev and production data lake. my code is as follows.

{
"name": "Newlinkservice",
"properties": {
"type": "AzureBlobFS",
"parameters": {
"storagename": {
"type": "String",
"defaultValue": "https://datalakenamehere.dfs.core.windows.net"
}
},
"annotations": [],
"typeProperties": {
"url": "@linkedService().storagename",
"encryptedCredential": "encrypted credentials removed"
}
}
}

I have dataset variable mapped but still when i run the pipeline it gives me following error

Operation on target Copy Data1 failed: ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'BadRequest'. Account: 'datalakename'. FileSystem: 'filesystemname'. Path: 'fullpath to file here'. Message: 'The request URI is invalid.'. RequestId: 'be4eec88-501f-00dc-6c81-02e625000000'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'BadRequest',Source=Microsoft.DataTransfer.ClientLibrary,'

Talib

3/25/2020 08:26:40 am

I am able to fix thanks :)

All Things Azure

Azure Data Factory (ADFv2) - Parameterizing Linked Services That Do Not Have Built-In Dynamic Content

Leave a Reply.

Categories