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

when I tried the below script in Script activity, I got same error.

CREATE EXTERNAL DATA SOURCE AzureStorage5 with 
( 
    TYPE =Blob_storage,
    LOCATION = @storageaccount,
    CREDENTIAL =AzureStorageCredential1
);

AFAIK, Script activity parameters might be only used as filters for the data like the below script.

select * from sample2 where Id=@Id

They may not be used for the above scenario or for passing the table while selecting from it like select * from @table_name.

So, for the location in the above script, create a set variable activity with your location value before the script activity and use that in SQL script with concat() or String interpolation as suggested in the comments.