How to pass dynamic @pipeline().parameters and value in store procedure activity with @json expression in Azure data factory

How to pass dynamic @pipeline().parameters and value in store procedure activity of Azure data factory

i have created the table 'testconfig' insert data Name &ProcedureParameterName

INSERT INTO testconfig

VALUES ('storeprocedurename','''{'',''"parm1": { "value": '', string(pipeline().parameters.parm1), '', "type": "Int64" },'',''"parm2": { "value": "'', pipeline().parameters.parm2, ''", "type": "String" }'', ''}''')

by lookup activity to pull store procedure name and store 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 store procedure activity dynamically passing parameters in store procedure activity by Json expression ..same script when i pass manually executed ..failed when passing from database lookup activity. Please guide me

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 store procedure activity dynamically passing parameters in store 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;