databaseazureazure-data-factoryazure-synapse

How to pass stored procedure parameters as dynamic content in copy activity using ADF


I have design copy activity pipeline in ADF and called a stored procedure and which take two parameters as input.

I want to use the parameters as dynamic content in copy activity when stored procedure call.

You can see the pipeline and stored procedure and two parameters:

You can see the pipeline and SP and two parameters

The issue that I am facing is that I am unable to use these two parameters as dynamic content and unable to write the json expression.

In this screenshot, there is option available to pass the values as dynamic:

In this picture there is option available to pass the values as dynamic

Is there any way to pass these two values as dynamic content in copy activity to a stored procedure call? Thank you for your consideration and time.


Solution

  • You can pass the parameters as dynamic content in the parameters value option. After creating the parameters, in the parameter value, click on Add dynamic content and you can add the dynamic expressions here.

    enter image description here

    Here, for sample I am passing the string and integer variables from ADF pipeline to the stored procedure parameters.

    enter image description here

    Execute the pipeline and you can check whether the values of these expressions passing correct to the parameters or not in the input section of the copy activity run details.

    enter image description here

    UPDATE:

    As your input parameter names are values are coming as a JSON from an activity, you can directly use the below dynamic expression.

    @json(replace(string(pipeline().parameters.json),'''',''))
    

    As I don't have any activity returning the JSON, I have stored the JSON object in a pipeline parameter json of object type and used it in the above dynamic expression. Instead of parameter, you can use your activity output which gives the required JSON object. Here, this expression converts the JSON object into a string and replaces the single quotes(') inside the string value with an empty space. After that, it converts the result string to JSON again which is the required JSON for passing parameters.

    enter image description here

    Copy activity input:

    enter image description here