question

NikunjPatel-2604 avatar image
0 Votes"
NikunjPatel-2604 asked PaulMathews-4081 commented

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

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?

![9043-adf-sample.png][1]

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:
[1]: /answers/storage/attachments/9043-adf-sample.png

azure-data-factory
adf-sample.png (10.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi all

actually got this to work with dynamic number of parameters, not "just" a single parameter with a different name. The trick was to pass into the activity a json string (or construct it in the activity) that looks like...

    {"Params":{"ParameterName1":{"value":"ParameterName1Value","type":"String"}}} for one parameter
    {"Params":{"ParameterName1":{"value":"ParameterName1Value","type":"String"}, "ParameterName2":{"value":"ParameterName2Value","type":"Int"}}} for two

then in the Copy Activity Source Side I have:

   @json(string(pipeline().parameters.SourceProcParameters)).Params

in the "Stored Proceedure Parameters" add dynamic content.

(full disclosure, I only have run it with proc with one parameter, but I'm fairly confident it will work with multiple parameters!)


0 Votes 0 ·
PaulMathews-4081 avatar image PaulMathews-4081 PaulMathews-4081 ·

And I think the same will work for Exec Procedure activity

0 Votes 0 ·
KranthiPakala-MSFT avatar image
1 Vote"
KranthiPakala-MSFT answered KranthiPakala-MSFT converted comment to answer

Hi @NikunjPatel-2604 ,

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:

9121-importparameter.png


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






importparameter.png (27.0 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @KranthiPakala for your answer.

0 Votes 0 ·

It works perfectly fine. As per question I get the answer So I mark accepted. But can you please suggest if I have dynamic number of prameters in procedure list then what we can do?(1 proc have 2 param other proc have 3 param and so on..)

0 Votes 0 ·

Hi @NikunjPatel-2604 ,

Glad to know that above suggestion helped to unblock you.

Regarding multiple SP parameters, I will give a try and will let you know once I have my findings ready.

0 Votes 0 ·

Have you get chance to look this scenario?

0 Votes 0 ·
KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered NikunjPatel-2604 commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NikunjPatel-2604,

Just checking to see if you have got a chance to share your feedback in ADF user voice forum. If so, please do share it here, so that other users with similar idea can up-vote and/or comment.



Thank you.

0 Votes 0 ·