How to pass ADF parameter values or System Function values as input parameter to a Stored Procedure that is dynamically invoked based on pipeline parameter based on an input json metadata?

George, Nitin Mathew 25

As per this URL one can invoke any SP dynamically by passing any parameters. But the challenge is when you want to pass on a ADF system variable or value from any other ADF variable/parameter to the dynamically fed in Input parameter of SPs, there is a challenge. I tried the below but it wasn't recognized by ADF.

Injected MetaData to argument- 'storedProcedureParameters'

{
    "ParamText": {
        "value": "HardcodedValue",
        "type": "String"
    },
    "ParamDate": {
        "value": {
            "value": "@pipeline().TriggerTime",
            "type": "Expression"
        }
        "type": "Datetime"
    }
}

Error:

Operation on target Execute Stored Procedure failed: ErrorCode=UserErrorInvalidParameterInStoredProcedure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'Value' is invalid for the stored procedure parameter '<Parameter>'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Exception,Message=System.Collections.Generic.Dictionary`2[System.String,System.Object] is not a valid value for <DataType>.,Source=System,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'

phemanth 6,810 Reputation points Microsoft Vendor

2024-01-03T08:57:58.0133333+00:00
@George, Nitin Mathew

Thanks for reaching MS Q&A

Here are a few things you can try to resolve this issue:

Check the data type: Make sure that the data type of the value you are passing to the stored procedure parameter matches the data type expected by the stored procedure. If the data types do not match, you may need to convert the value to the correct data type before passing it to the stored procedure.

Check the format: Make sure that the format of the value you are passing to the stored procedure parameter is correct. For example, if you are passing a date/time value, make sure that the value is in the correct format (e.g., "YYYY-MM-DDTHH:MM:SSZ").

Check the expression: If you are passing an expression to the stored procedure parameter, make sure that the expression is evaluated correctly and returns the expected value. You can test the expression by using it in a separate activity and checking the output.

Check the parameter name: Make sure that the parameter name you are using in the stored procedure matches the parameter name expected by the stored procedure. If the parameter names do not match, the stored procedure may not recognize the parameter.

If none of these solutions work, you may need to provide more information about your specific scenario.
George, Nitin Mathew 25 Reputation points

2024-01-03T09:42:41.55+00:00
@phemanth Thank you for your response, TBH I did try replacing a Datetime parameter with an INT based value.

I did try the following-

Test the SP in SSMS -It worked fine; my SP is just SELECT the values passed as input parameter. For eg. SELECT @ParamText AS Text, @ParamInt AS Value

With the INT based Parameter, when I dynamically pass the value hardcoded -it works. For eg.

"ParamInt": { "value": "22", "type": "Int32" }

The same doesn't work when I need to dynamically fetch the value... say from a pipeline parameter. For eg.

"ParamInt": { "value": { "value": "@pipeline().parameters.MyIntPipelineParameter", "type": "Expression" } "type": "Int32" } ``` 1. The points #3 & #4 in your response are checked. I only need to understand- *is there any issue with ADF while passing SP parameter values with its type as 'Expression'*?
phemanth 6,810 Reputation points Microsoft Vendor

2024-01-04T05:33:52.8466667+00:00
@George, Nitin Mathew

A potential workaround suggested is to put the parameter directly in the pipeline parameter, and make sure your parameter/variables are enclosed with curly brackets { } and associated with @. Here’s an example:

{ "ParamInt": { "value": "@{pipeline().parameters.MyIntPipelineParameter}", "type": "Int32" } }

Another workaround suggested is to convert the parameter to a JSON object using the @json(string(pipeline().parameters.param1)) expression. This could help ensure that the parameter is correctly interpreted as an object, not a string

If the issue persists,please do let us know.
George, Nitin Mathew 25 Reputation points

2024-01-04T13:00:32.2266667+00:00
A. Testing the Stored Procedure by manually passing the ADF values as inputs-

B. The MetaData of JSON specifying the Stored Procedure's Input parameters with a dynamic ADF expression following the format as quoted by Stack Overflow-

C. Just to prove that there is no issue with the Input parameter & its value, below is the output from another task that succeeded after passing this TaskID as an input parameter-

D. The error message after trying this-

E. Expression used to inject Input Parameter-

@json(activity('Invoke ETL Processing Task').output.firstRow.MappingJSON)

and also tried-

@json(string(activity('Invoke ETL Processing Task').output.firstRow.MappingJSON))

resulted in same error as-

Summary:

Tried both work arounds and did not work. This issue didn't seem something that easy to me. Also remember this has to be this dynamically injected through a DB. we cannot hardcode the value into a pipeline parameter.

@phemanth
phemanth 6,810 Reputation points Microsoft Vendor

2024-01-05T09:25:27.86+00:00

@George, Nitin Mathew

checking with the internal team will get back to you shortly.
George, Nitin Mathew 25 Reputation points

2024-01-06T16:59:41.3+00:00

@phemanth Thank you, looking forward to hear from you.
George, Nitin Mathew 25 Reputation points

2024-01-08T13:09:43.3266667+00:00

@phemanth any follow-up on this with the internal team, is highly appreciable.
George, Nitin Mathew 25 Reputation points

2024-01-11T06:02:33.6966667+00:00

@phemanth A gentle reminder.
phemanth 6,810 Reputation points Microsoft Vendor

2024-01-11T10:07:25.41+00:00

@George, Nitin Mathew
could you please share your ADF pipeline implementation details with screenshots?
George, Nitin Mathew 25 Reputation points

2024-01-11T18:10:04.5233333+00:00

@phemanth hope this helps!
George, Nitin Mathew 25 Reputation points

2024-01-16T07:38:33.3833333+00:00

@phemanth will appreciate a quicker resolution on this.
phemanth 6,810 Reputation points Microsoft Vendor

2024-01-16T09:04:42.35+00:00
@George, Nitin Mathew One possible solution is to pass a JSON string into the activity that represents your parameters. Here’s an example of how you might structure this JSON:

{ "Params": { "ParameterName1": { "value": "ParameterName1Value", "type": "String" }, "ParameterName2": { "value": "ParameterName2Value", "type": "Int" } } }

Then, in the “Stored Procedure Parameters” section of your Copy Activity Source Side, you can use the following expression:

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

This approach allows you to pass a dynamic number of parameters to your stored procedure. please go through these threads: https://learn.microsoft.com/en-us/answers/questions/31693/how-to-pass-dynamic-parameter-name-and-value-in-st https://stackoverflow.com/questions/68784681/how-to-set-parameter-values-for-stored-procedure-activity-in-adf-pipeline-becaus Another approach is to modify your SQL table schema to include the ProcedureParameterName and ProcedureParameterValue. Then, you can retrieve these values from your lookup activity and pass them to the ForEach activity. You can then pass the SP parameters name & value dynamically using this expression in Stored procedure activity settings:

@item().ProcedureParameterName = @item().ProcedureParameterValue
George, Nitin Mathew 25 Reputation points

2024-01-16T11:18:56.44+00:00

@phemanth It is very disappointing to see such responses; I will accept if some generative tool had auto populated this solution. But as a human being we are expected to read & understand the basic differences. You seem to write something for the sake of responding, if you read my question properly or if you had gone through the references you posted me, you will realize it.

Please understand, I am not an idiot to spend my time writing & explaining you with pictures. If you wish to help please pull in a sensible person from the right team. Sorry for sounding rude but, I still believe it is necessary for having such a response. If you still didn't understand you just reposted the same references that I had quoted here and, JFYI- Both asks are different even though they related to the same. Maybe the Technical team can comment if the ask is do-able or not.

Accepted answer

phemanth 6,810 Reputation points Microsoft Vendor

2024-01-17T07:56:48.8+00:00

@George, Nitin Mathew
Hi

I apologize for inconvenience actually I communicated with internal team person.

we tried from our end and found in your case it is not possible to pass expression in JSON dynamically ADF does not consider expression as expression it considers as string so it will end up with error.

It is a limitation.

i see you use lookup activity. To sort this, you can pass the actual value instead of expression. you should do by adding set variable activity it before lookup activity and pass inside the set variable activity.

Or else

if you want to pass expression you should follow the regular approach as show in picture below

Hope this helps. Do let us know if you any further queries.
Please sign in to rate this answer.
George, Nitin Mathew 25 Reputation points

2024-01-17T09:09:49.2766667+00:00

@phemanth Thank you for taking it in the right spirit, my apologies as well. Appreciate your efforts in getting back on this more specifically. Just wanted to confirm on this limitation. BTW, if there is a way to request the MS ADF Technical/Product team to have this requirement on backlog, please let me know. I think if ADF has this small thing addressed, then it could do wonders!

phemanth 6,810 Reputation points Microsoft Vendor

2024-01-17T14:41:43.46+00:00

@George, Nitin Mathew It's actually good feedback item to consider. I would encourage you to consider creating feedback item for this limitation using below link. Product team closely monitor feedbacks and consider for future implementations.

https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c?query=hubspot

please, don't forget to click Accept Answer and Yes this will be helpful to community. And, if you have any further query do let us know.

George, Nitin Mathew 25 Reputation points

2024-01-18T08:24:52.9366667+00:00

Thanks again, have requested for this feature here . Wish to get some upvotes.

Arlind Tahiri 0 Reputation points

2024-05-15T10:04:26.5933333+00:00

Any updates to this? What was your workaround?
Sign in to comment

Share via

How to pass ADF parameter values or System Function values as input parameter to a Stored Procedure that is dynamically invoked based on pipeline parameter based on an input json metadata?

0 additional answers