Use JSON to Feed For Each Loop in Data Factory

Question

New to Data Factory and I'm struggling with the following issue:

I have a web activity thats calling an API and returning the following JSON:

{
"ResponseCode": 200,
"ResponseText": "OK",
"Data": {
    "ramco_purchaseordershipment": "ramco_purchaseordershipment",
    "ramco_ramco_paymentschedule_cobalt_duesoption": "ramco_ramco_paymentschedule_cobalt_duesoption",
    "cobalt_accountingintegrationbatch": "cobalt_accountingintegrationbatch",
    "opportunitycompetitors": "OpportunityCompetitors"}

}

Which represent Entity Names from a Dynamics 365 DB.

I add a For-Each activity setting items to:

@array(activity('Web1').output.Data)

which ends up giving me a single item array which is not what I want.

What I'm trying to accomplish is to iterate through ramco_purchaseordershipment, ramco_ramco_paymentschedule_cobalt_duesoption, etc using then trigger another pipeline using each value as a parameter.

I know its something stupid but I've been staring at it all afternoon with no luck.

Thanks!

Michael

Answer 1

I created a simple test to achieve that. Here I'm using a lookup activity to return a json file the same as yours.
My idea is:

• Convert this json object into a string.
• Add },{ to the string.
• Split it into a string array by commas.
• Finally traverse this array .
• Convert the string into a json object and pass it to the parameter of the next pipeline.

1. Declare an array type variable.

2. Use expression @split(replace(string(activity('Lookup1').output.value[0].Data),',','},{'),',') to get the string array. Here you need to replace activity('Lookup1').output.value[0].Data with activity('Web1').output.Data.
   

3. Foreach the string array.

4. Add dynamic content @json(item()), it will parse string to json

5. The input of the Execute Pipeline1 is as follows:

    "parameters": {
        "Para1": {
            "ramco_purchaseordershipment": "ramco_purchaseordershipment"
        } 


    "parameters": {
        "Para1": {
            "ramco_ramco_paymentschedule_cobalt_duesoption": 
         "ramco_ramco_paymentschedule_cobalt_duesoption"
        }

    "parameters": {
        "Para1": {
            "cobalt_accountingintegrationbatch": "cobalt_accountingintegrationbatch"
        }

    "parameters": {
        "Para1": {
            "opportunitycompetitors": "OpportunityCompetitors"
        }
    }