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

Chris Kingdon 30 Reputation points
2023-04-21T17:37:33.1133333+00:00

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: bwbKb

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.

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.
4,473 questions
0 comments No comments
{count} vote

Accepted answer
  1. ShaikMaheer-MSFT 38,201 Reputation points Microsoft Employee
    2023-04-25T15:32:01.8533333+00:00

    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.

    User's image

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

    Hope this helps.


0 additional answers

Sort by: Most helpful