How to use script parameters in an Azure Data Factory Script Activity for Snowflake?

Question

In an Azure Data Factory pipeline I have an Activity Script that is executing an SQL statement on Snowflake. Everything works fine until I begin to use a script parameter in my statement, resulting in the statement encountering a syntax error. The SQL I'm trying to execute is:

USE ROLE ?

According to the tooltip for the script parameters, the tool says: "For Oracle and Snowflake connectors, only positional parameter is supported. Use "?" as placeholders in the query for the parameter. The order of "?" needs to match the order of the provided parameters below.". However no matter what I try in my statement, I always seem to get a syntax error: ERROR [42000] SQL compilation error: syntax error line 1 at position 9 unexpected '?'. Can someone offer any advice as to what I am doing wrong? Below is a screenshot of the SQL being execute, the single parameter, and the variations I have already tried to get this to work with no success:

1. Using a ? or "?" in the SQL without the script parameter named.
2. Using a ? or "?" in the SQL with the script parameter named either: Param1 or ?.
3. Using a @Param1 in the SQL with the parameter named Param1.

The Example Script that Errors

Answer 1

For now @AnnuKumari-MSFT , and anyone else that comes across this, I've been able to solve this issue by using SQL variables like below:

SET MYVAR = ?;
USE ROLE IDENTIFIER($MYVAR);

Alternatively, pipeline parameters can be used like so:

USE ROLE @{pipeline().parameters.role};

Answer 2

Hi Luke-3675 , Thanks for the updates on the issue. Kindly check if the following blog post can be of your help: Execute SQL Scripts on Snowflake using Azure Data Factory To pass the parameter , you can simply use @ as shown in below image:

For better help, I would recommend you to share the exact piece of code you are trying to execute. I am yet to explore using '?' as a placeholder for oracle or snowflakes DB, but it seems the feature is similar to how it was the case in SSIS. "For Snowflake and Oracle, you have to use question marks as placeholder (just like in SSIS)."

I am investigating more, and will update you further if I come across more relevant information. However, if the above response helped, kindly accept the answer as accepted answer helps community as well. Thankyou.

Answer 3

Hello Luke, Based on the error message you provided, it seems that Snowflake is not able to recognize the "?" placeholder in your SQL statement as a valid syntax. To resolve this issue, you can try the following steps:

1. Replace the "?" placeholder with a positional parameter in your SQL statement. You can use a numeric index to specify the position of the parameter in the query, such as "@1", "@2", and so on.
2. Make sure that the order of the positional parameters in the SQL statement matches the order of the parameters specified in the script parameters section of the activity. For example, if you have only one parameter named "Param1", then it should be the first parameter in the query, and you should use "@1" as the placeholder in the SQL statement.
3. When you specify the value of the script parameter, make sure that you use the correct syntax. For example, if your script parameter is named "Param1", then you should use the following syntax to set its value: "Param1 = <value>". Make sure that you enclose the value in quotes if it is a string, and that you do not include the angle brackets "<>". This is an example: SELECT * FROM myTable WHERE myColumn = @1 And here is an example of how to specify the value of the "Param1" parameter in the script parameters section: Param1 = 'myValue' Please replace "myTable", "myColumn", "myValue", and any other placeholders in the SQL statement with your own values. Also, make sure that you enclose any string values in single quotes. I hope this helps you to resolve your issue. If you still have trouble, please provide more details on the structure of your SQL statement and the script parameter that you are using.