How to pass dynamic parameter name and value in store procedure activtiy of Azure data factory V2?

Question

I'm working on creating one ADF Pipelines. Purpose of this pipeline is to execute list of store procedures in sequence.

So far I am use following method:

1. Created a SQL table with list of all store procedures. (Table having following schema : Id,ProcedureName,ProcedureparameterValue)
2. In ADF pipeline, Use lookup component to get all list of store procedures from above table.
3. Next use ForEach component , which iterate one by one item from above list.
4. Inside ForEach activity, Use store procedure component and pass dynamic store procedure name using @Item().ProcedureName.

But I am stuck in pass dynamic parameter. As each store procedure have different parameter name. Can anyone have any idea How we can pass dynamic parameter in Store procedure in ADF?

Note: Currently we assume we have only one parameter in all store procedures. Later on we can extend for more parameters. Also not found Azure Data Factory tag So I use adfs.

Find attached screen shot for pipeline:

Answer 1

Hi @Nikunj Patel ,

Welcome to Microsoft Q&A and thanks for your query.

Since you are having different parameter names for each SP, I would recommend having your SP list table schema like :

Id, ProcedureName, ProcedureParameterName, ProcedureParameterValue

Then you can retrieve the ProcedureParameterName and ProcedureParameterValue from your lookup activity and pass it to ForEach activity.

And finally pass SP parameters name & value dynamically as using this expression in Stored procedure activity settings : @item().ProcedureParameterName= @item().ProcedureParameterValue,

Example as below:

Hope this helps. Let us know if you have further query.

----------

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi @NikunjPatel-2604,

Thanks for following up. Looks like ADF parameterization of sp parameters does doesn't work as expected when your stored procedures have variable number of parameters. I would recommend you to provide your feedback/suggestion in ADF user voice forum and do share the feedback link here once it is created so that other users can up-vote your idea.

ADF user voice forum: https://feedback.azure.com/forums/270578-data-factory

All the feedback shared in this forum are actively monitored and reviewed by ADF engineering team.

Hope this helps.

---

Thank you.