Azure Synapse Pipeline Script Activity: "Incorrect Syntax" Error when using script parameters

Question

I'm currently trying to write a script that will be run by an Azure Synapse pipeline to initialize my serverless database. As part of that I need to use a parameter so that I can switch out the storage account easily between the prod and non-prod versions of synapse. For some reason I can't get the script parameter to work. Here is my SQL query:

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'bronze_data')
BEGIN
    CREATE EXTERNAL DATA SOURCE bronze_data WITH (LOCATION = @storageAccountUrl);
END

Then I define a new script parameter like this:

However when I run the pipeline I get the following error: errorCode 2011, Incorrect syntax near '@storageAccountUrl' If I hardcode the location everything works just fine. I am able to make it work by using dynamic content and just using pipeline parameters but that's a lot messier to use (no syntax highlighting and often you have to do string concatenation). Am I missing something here or is there some kind of bug in Azure?

Edit: I just found this post that is the same error: https://learn.microsoft.com/en-us/answers/questions/1005399/azure-synapse-script-parameter-for-external-table. Their solution works if I use pipeline parameters but it still doesn't pull from the script parameters.

Answer 1

Hi Chris Kingdon,

Thank you for posting query in Micrsoft Q&A Platform.

When I tested script parameters with simple SELECT statement, Its working. it looks with CREATE statement script parameters are not supported and we should use pipeline parameters.

Kindly consider using pipeline parameters. I am checking more with internal team about script parameters in this scenario and share updates.

Hope this helps.