in ADF how to use script parameters in the new script activity

Question

in ADF how to use script parameters in the new script activity

Smithila Dandina 0

I need to need script parameters to run a stored procedure in snowflake but the new upgraded script activity doesn't support script parameters and I need to pass them dynamically into snowflake stored procedure. Can someone please assist me with this ?

Smaran Thoomu 32,615 Reputation points Microsoft External Staff Moderator

2024-11-04T15:09:38.51+00:00

@Smithila Dandina Just checking in to see if the below answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Smaran Thoomu 32,615 Reputation points Microsoft External Staff Moderator

2024-11-04T15:09:38.51+00:00

@Smithila Dandina Just checking in to see if the below answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Vinodh247 40,556 MVP Volunteer Moderator

Hi Smithila Dandina,

Thanks for reaching out to Microsoft Q&A.

Approach 1:

To work around the lack of script parameter support in the new Script Activity in ADF, you can pass parameters dynamically using expressions to construct the SQL command with the desired parameters directly in the activity. Here’s a way to handle it:

Create a Dynamic SQL Statement: In the Script Activity, construct the SQL command by concatenating the parameter values using ADF expressions. For example

EXECUTE PROCEDURE my_stored_procedure('${myParameter1}', '${myParameter2}')

Use Pipeline Variables: Define the parameters as pipeline variables (e.g., myParameter1, myParameter2). In the Script Activity, reference these parameters dynamically within your SQL script using @concat.
Build the Script Dynamically:
- in the SQL statement field of the Script Activity, use the @concat expression to build the command:
  
  @concat('EXECUTE PROCEDURE my_stored_procedure('', pipeline().parameters.myParameter1, '', '', pipeline().parameters.myParameter2, '')')
Pass Parameters to Script: If these parameters are dynamically provided, ensure they are set as pipeline parameters or variables that are accessible within the Script Activity.

Approach 2:

You can pass parameters dynamically to your Snowflake stored procedure by using Azure Functions as an intermediary layer.

Here's highlevel approach:

Create an Azure Function: Set up an Azure Function that will be triggered by your ADF pipeline.

Pass Parameters: Use the parameters from your ADF pipeline to pass them to the Azure Function.

Call Stored Procedure: In the Azure Function, use the parameters to call your Snowflake stored procedure.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Smithila Dandina 0 Reputation points

2024-10-31T16:14:42.3866667+00:00

thank you @Vinodh247 for providing solutions. I did try approach 1 but the script activity is considering this as a string "pipeline().parameters.myParameter2" and entering the value as pipeline().parameters.myParameter2 instead of picking the right value. I tried different methods but doesn't seem to work. Do you have any other suggestions here ?
Smithila Dandina 0 Reputation points

2024-10-31T16:19:15.5666667+00:00

Thank you Vinodh247 for providing these solutions but looks like script activity is unable to retrieve the value of the parameter in the stored procedure, instead of adding the value of parameter its just adding it as "pipeline().parameters.myParameter1". I tried various methods here but nothing is working as expected. Do u have any more suggestions here ?
Smaran Thoomu 32,615 Reputation points Microsoft External Staff Moderator

2024-11-01T01:47:59.9766667+00:00
Hi Smithila Dandina

I understand that when you try to use pipeline parameters directly within the Script Activity, it’s interpreting them as strings rather than resolving the values dynamically. This is a common limitation when working with the current setup in Script Activity for Snowflake.
Here are a few alternative approaches that might work:

You could try using the Set Variable activity within your pipeline to assign values to the required parameters before passing them to the Script Activity. Construct the SQL command with expressions in the Set Variable activity, then reference this variable directly in the Script Activity to avoid direct expression handling.

Another option is to create the SQL command as a pipeline parameter (e.g., @concat('EXECUTE PROCEDURE my_stored_procedure(', pipeline().parameters.myParameter1, ', ', pipeline().parameters.myParameter2, ')')). Then, pass this constructed SQL command as a single variable to the Script Activity.

Since Approach 2 mentioned an Azure Function, you could go further by setting up either an Azure Function or Logic App as an intermediary to accept parameters, construct the SQL, and call Snowflake. This can provide more flexibility in parameter handling.

I hope one of these alternatives helps! Let me know If you have any further questions.

Share via

in ADF how to use script parameters in the new script activity

1 answer

Your answer