Parametrization for connection string is not working in linked service

Peter Michalik 0 Reputation points
2024-11-07T19:55:32.4766667+00:00

Hi there,

I have solution - data pipeline in Synapse taking data from mysql database. I need to parametrize it to be able run process across many environemnts (CI/CD). I set many linked services with code referencing on variable in yml file in style like this

"url": "#{SYNW.adlsaaa}#"

It was still working and after my CD run it promoted correct value from yml file to dev/test/stage branch withou any issues. When I try to do the same thing within linked service referencing to mysql - it will promote changes to dev/test/stage from my main branch - but it will break the main branch and getting errormessage:

"Could not load resource 'ls_mysql_empadm_aaa'. Please ensure no mistakes in the JSON and that referenced resources exist. Status: Connection String Syntax Error, Position: 23, Possible reason: Connection String Syntax Error, Position: 23"

This is my code

{

	"name": "ls_mysql_empadm_aaa",
	"type": "Microsoft.Synapse/workspaces/linkedservices",
	"properties": {
		"connectVia": {
			"referenceName": "AutoResolveIntegrationRuntime",
			"type": "IntegrationRuntimeReference"
		},
		"type": "AzureMySql",
		"typeProperties": {
			"connectionString": "#{SYNW.mysqlstringaaa}#",
			"password": {
				"secretName": "mysql-empadm",
				"store": {
					"referenceName": "ls_kv_master",
					"type": "LinkedServiceReference"
				},
				"type": "AzureKeyVaultSecret"
			}
		},
		"annotations": []
	}
}
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,067 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,014 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Pinaki Ghatak 5,305 Reputation points Microsoft Employee
    2024-11-08T10:38:36.7+00:00

    Hello @Peter Michalik

    It seems like there is a syntax error in the connection string of your linked service. The error message suggests that the error is at position 23. To parametrize the connection string in your linked service, you can use the following syntax:

    {
    "connectionString": { "value": "@{linkedService().connectionString}", 
    "type": "Expression" 
    }
    
    

    Replace the connectionString property in your code with the above syntax and try again.

    Make sure to replace the linkedService().connectionString with your own variable name. Also, make sure that the variable SYNW.mysqlstringaaa is defined and has a valid connection string value.

    I hope this helps.

    0 comments No comments

  2. Peter Michalik 0 Reputation points
    2024-11-08T12:17:15.1266667+00:00

    Hi @Pinaki Ghatak ,

    thanks for your answer. The syntax you provided with me was working from the "syntax" perspective and also after running my CD pipeline I was able to see correct connection strings values per einvironemnt in my repos - dev string for dev environment, test for test env etc...The issue is within the synapse it did not fill that string to linked service setup -see picture.

    User's image

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.