Call multiple stored procedures in foreach Azure Pipeline Data Factory-CodePudding

My requirement is to call multiple stored procedures in Data Factory Pipeline, using lookup activity. I'm executing a Select query which list me the total stored procedure names in a json format as shown below:

  {
        "count": 4,
        "value": [
            {
                "ProcedureName": "DimAccount"
            },
            {
                "ProcedureName": "DimAct"
            },
            {
                "ProcedureName": "DimActivity"
            },
            {
                "ProcedureName": "DimyType"
            }
        ],
        "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West US)",
        "billingReference": {
            "activityType": "PipelineActivity",
            "billableDuration": [
                {
                    "meterType": "ManagedVNetIR",
                    "duration": 0.016666666666666666,
                    "unit": "DIUHours"
                }
            ]
        },
        "durationInQueue": {
            "integrationRuntimeQueue": 0
        }
    }

After this I added a ForEach activity and then inside foreach I added an append activity to get all stored procedure names in a separate variable called spName declared as variable Array parameter at outer canvas of the pipeline. During the execution of the pipeline I see appendactivity variable gets holds the Individual record as below

{
    "variableName": "spName",
    "value": "DimyType"
}

so on

{
        "variableName": "spName",
        "value": "DimAccount"
}

Similarly for other values too with all 4 append activity holds an individual array values, I added a stored procedure activity and trying to call the with below expression from appendactivity

  @string(item().ProcedureName)

From my Append activity I need to extract only the stored procedure name out of the json i.e

"value": "DimAct"

and pass it to stored procedure activity as "DimAct" stored procedure name

Instead I see again whole json pull to stored procedure Activity as below

{
    "storedProcedureName": {
        "ProcedureName": "DimAct"
    }
}

Due to this I see error in the stored procedure activity

Any help would be really appreciated, I'm quite new to Azure environment I found Append Variable activity could be a good match for this requirement, I may be wrong any other activity or approach to achieve my goal with new suggestions are also welcome

CodePudding user response：

Tried to reproduce similar issue and got same error

The issue is causing due to append variable append the values into array and then it is passing that array to stored procedure.

Solution My sample output of Lookup Activity

There is no need to use append activity. just pass lookup output to foreach by @activity('Lookup1').output.value

Then create stored procedure activity in it and give dynamic value as @item().SPECIFIC_NAME it will run all the stored procedures.

Pipeline executed successfully