Data Preview in Data Flow gives null values only for JSON with sub-arrays

Question

I have a JSON file as the source (with sub-arrays), which is the response from an api call. I need to copy this JSON into SQL server table, row by row.

I am trying to use a flatten transformation in ADF data flow.

The issue I am facing is, in every step of the data flow, the sub-arrays are always coming as NULL in the output.

A sample from the source file is as shown below:

{
  "SOAP-ENV:Envelope": {
    "SOAP-ENV:Body": {
      "ns1:getAllMaintenanceResponse": {
        "package": {
          "status": {
            "code": {
              "xsi:@type": "tns:StatusCode",
              "_value_": "OK"
            },
            "description": {
              "xsi:@type": "tns:StatusDescription",
              "_value_": "OK"
            },
            "xsi:@type": "tns:Status"
          },
          "maintenance": {
            "item": [
              {
                "assetMaintenanceID": {
                  "xsi:@type": "xsd:integer",
                  "_value_": 183982
                },
                "assetID": {
                  "xsi:@type": "xsd:integer",
                  "_value_": 4934334
                },
                 "parts": {
                  "item": {
                    "part": {
                      "xsi:@type": "xsd:string",
                      "_value_": "ENGINE OIL\t"
                    },
                    "quantity": {
                      "xsi:@type": "xsd:float",
                      "_value_": 1.0
                    },
                    "xsi:@type": "tns:Part"
                  },
                  "xsi:@type": "SOAP-ENC:Array",
                  "SOAP-ENC:@arrayType": "tns:Part[1]"
                },
                "labour": {
                  "xsi:@nil": true,
                  "xsi:@type": "SOAP-ENC:Array",
                  "SOAP-ENC:@arrayType": "tns:Labour[0]"
                },
              {
                "assetMaintenanceID": {
                  "xsi:@type": "xsd:integer",
                  "_value_": 186334
                },
                "assetID": {
                  "xsi:@type": "xsd:integer",
                  "_value_": 4653204
                },
                "parts": {
                  "item": [
                    {
                      "part": {
                        "xsi:@type": "xsd:string",
                        "_value_": "Baron de regulación"
                      },
                      "quantity": {
                        "xsi:@type": "xsd:float",
                        "_value_": 1.0
                      }
                    },
                    {
                      "part": {
                        "xsi:@type": "xsd:string",
                        "_value_": "Baron de regulación"
                      },
                      "quantity": {
                        "xsi:@type": "xsd:float",
                        "_value_": 1.0
                      }
                     }
                    }
                    }
                  ],
                  "xsi:@type": "SOAP-ENC:Array",
                  "SOAP-ENC:@arrayType": "tns:Part[2]"
                },
                "labour": {
                  "item": [
                    {
                      "name": {
                        "xsi:@type": "xsd:string",
                        "_value_": "Instalación Baron de regulación"
                      },
                    "cost": {
                        "xsi:@type": "xsd:float",
                        "_value_": 20
                      },
                      "xsi:@type": "tns:Labour"
                    },
                    {
                      "name": {
                        "xsi:@type": "xsd:string",
                        "_value_": "Instalación cañería de freno"
                      },
                      "cost": {
                        "xsi:@type": "xsd:float",
                        "_value_": 15
                      },
                      "xsi:@type": "tns:Labour"
                    }
                  ],
                  "xsi:@type": "SOAP-ENC:Array",
                  "SOAP-ENC:@arrayType": "tns:Labour[2]"
                },
            "xsi:@type": "SOAP-ENC:Array",
            "SOAP-ENC:@arrayType": "tns:Maintenance[2]"
          },
          "xsi:@type": "tns:MaintenancePackage"
        }
      }
    },
    "SOAP-ENV:@encodingStyle": "http://schemas.xmlsoap.org/soap/encoding/"
  }
}

• I created a Data Flow in ADF

• Source is the JSON file given above, as a Dataset

• Flatten activity with mapped columns only for 2 items from the main array and 1 item each from each of the sub-array

• The sub-array values are coming as NULL

  ADF Data Flow - Data Preview in Flatten Transformation