How to pass dynamic @pipeline().parameters and value in stored procedure activity with @json expression in Azure Data Factory

Question

How to pass dynamic @pipeline().parameters and value in stored procedure activity of Azure Data Factory?

I have created a table testconfig and insert data Name &ProcedureParameterName

INSERT INTO testconfig
VALUES ('storedprocedurename', '''{'',''"parm1": { "value": '', string(pipeline().parameters.parm1), '', "type": "Int64" },'',''"parm2": { "value": "'', pipeline().parameters.parm2, ''", "type": "String" }'', ''}''')

By lookup activity to pull stored procedure name and stored procedure parameters from database.

By using foreach activity, I am passing the @activity('Get Procedure Name').output.value

Inside for each activity the set variable

@concat(item().ProcedureParameterName)

In the stored procedure activity dynamically passing parameters in stored procedure activity by Json expression. Same script when I pass manually executed. It failed when passing from database lookup activity. Please guide me

Answer 1

It looks like you are giving combination of strings and pipeline expressions in the insert query, so that it can generate required JSON string using concat() function to pass the parameter values to the JSON from lookup activity.

In the stored procedure activity dynamically passing parameters in stored procedure activity by Json expression ..same script when I pass manually executed ..failed when passing from database lookup activity.

ADF dynamic expression does not supports nested dynamic expressions (If the dynamic expressions are in strings, it cannot execute that expression). When you are directly passing the JSON to the stored procedure using concat(), the dynamic expression identifies the parameters and parameter values will be extracted. But when you are trying to get those using lookup activity output, the parameter expression will become a nested expression. ADF dynamic expression only identifies the lookup activity output and recognizes the inner dynamic expression as a string ("pipeline().parameters.parm1").

To pass the parameter values to the JSON dynamically, don't give the same expression that you want to give to the concat(). You can change the insert query like below.

insert into testconfig values('mysp1','{"parm1":{"value":pipeline().parameters.parm1,"type":"Int64"},"parm2":{"value":"pipeline().parameters.parm2}","type":"String"}}')

Get this table values using lookup activity and give the lookup output array to for-each. Inside for-each, replace the pipeline().parameters.<parameters> in the item().ProcedureParameterName with required parameters as shown below.

@replace(replace(item().ProcedureParameterName,'pipeline().parameters.parm1',string(pipeline().parameters.parm1)),'pipeline().parameters.parm2',pipeline().parameters.parm2)

The JSON string will be generated like below in each iteration.

Now, you can pass this string variable to your stored procedure parameters either by using Stored procedure activity or a Script activity as well.

In the Script activity, you can directly call the stored procedure using the below script. Here,

DECLARE @Json NVARCHAR(MAX)
SET @Json = N'@{variables('myjson')}'

EXEC @{item().Name} @JsonInput = @Json;