Parameterize linked services in Azure Data Factory and Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

You can now parameterize a linked service and pass dynamic values at run time. For example, if you want to connect to different databases on the same logical SQL server, you can now parameterize the database name in the linked service definition. This prevents you from having to create a linked service for each database on the logical SQL server. You can parameterize other properties in the linked service definition as well - for example, User name.

You can use the UI in the Azure portal or a programming interface to parameterize linked services.

Tip

We recommend not to parameterize passwords or secrets. Store all secrets in Azure Key Vault instead, and parameterize the Secret Name.

Note

There is open bug to use "-" in parameter names, we recommend to use names without "-" until the bug is resolved.

For a seven-minute introduction and demonstration of this feature, watch the following video:

Supported linked service types

All the linked service types are supported for parameterization.

Natively supported in UI: When authoring linked service on UI, the service provides built-in parameterization experience for the following types of linked services. In linked service creation/edit blade, you can find options to new parameters and add dynamic content. Refer to UI experience.

  • Amazon Redshift
  • Amazon S3
  • Amazon S3 Compatible Storage
  • Azure Blob Storage
  • Azure Cosmos DB for NoSQL
  • Azure Databricks Delta Lake
  • Azure Data Explorer
  • Azure Data Lake Storage Gen1
  • Azure Data Lake Storage Gen2
  • Azure Database for MySQL
  • Azure Database for PostgreSQL
  • Azure Databricks
  • Azure File Storage
  • Azure Function
  • Azure Key Vault
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure Synapse Analytics
  • Azure Table Storage
  • Dataverse
  • DB2
  • Dynamics 365
  • Dynamics AX
  • Dynamics CRM
  • File System
  • FTP
  • Generic HTTP
  • Generic REST
  • Google AdWords
  • Google BigQuery
  • Informix
  • MariaDB
  • Microsoft Access
  • MySQL
  • OData
  • ODBC
  • Oracle
  • Oracle Cloud Storage
  • PostgreSQL
  • Salesforce
  • Salesforce Service Cloud
  • SAP CDC
  • SAP HANA
  • SAP Table
  • ServiceNow (not supported in ServiceNow legacy)
  • SFTP
  • SharePoint Online List
  • Snowflake
  • SQL Server

Advanced authoring: For other linked service types that are not in above list, you can parameterize the linked service by editing the JSON on UI:

  • In linked service creation/edit blade -> expand "Advanced" at the bottom -> check "Specify dynamic contents in JSON format" checkbox -> specify the linked service JSON payload.
  • Or, after you create a linked service without parameterization, in Management hub -> Linked services -> find the specific linked service -> click "Code" (button "{}") to edit the JSON.

Refer to the JSON sample to add parameters section to define parameters and reference the parameter using @{linkedService().paramName}.

UI Experience

JSON

{
	"name": "AzureSqlDatabase",
	"properties": {
		"type": "AzureSqlDatabase",
		"typeProperties": {
			"connectionString": "Server=tcp:myserver.database.windows.net,1433;Database=@{linkedService().DBName};User ID=user;Password=fake;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
		},
		"connectVia": null,
		"parameters": {
			"DBName": {
				"type": "String"
			}
		}
	}
}

Store credentials in Azure Key Vault