Azure data factory - Data flow External call - specify response body schema

Question

I .. simply do not understand what I should do with this. I have a REST call that gets the list of Projects. I set up that as a source to the data flow - having a Rest Linked service feed the data.

The JSON from the REST call is list of Projects objects Something like ...

{
"_id": "5a04b1a0",
"name": "Some name - as string",
"number": "179",
"value": null,
"state": "PUBLISHED",
"Sealed": 0,
"ndaRequired": 0,
"public": 1
}

and there can be hundreds of those. After the list is obtained, I need to go through each one and make additiona API call to https:///projects/<{_id}> where _id is a project ID (from above) This call will give me individual project with ADDITIONAL fields - nested "Packages" array

{
"_id": "5a04b1a0",
"name": "Some name - as string",
"number": "179",
"value": null,
"state": "PUBLISHED",
"Sealed": 0,
"ndaRequired": 0,
"public": 1,
"Packages": [
    {
        "_id": "59a0471db3",
        "projectId": "5a04b1a0",
        "name": "some",
        "number": "9250",
        "keywords": [
            "keyWord"
        ],
        "state": "PUBLISHED",
        "dateStart": null
    },
    {
        "_id": "934234kkd93",
        "projectId": "5a04b1a0",
        "name": "some other",
        "number": "24349374",
        "keywords": [
            "keyWord, keyword, keyword"
        ],
        "state": "UNKNOWN",
        "dateStart": null
    }
}

I do not know how (The syntax??) to specify the response schema for each individual "GetPackages" external call. please help. In the "Data Preview" of the FlattenProjects stage, I get the correct list for Projects, and after "Derived Column" I get the additional "ProjectID" as a url to be added. If I test the complete individual URL in postman - it works, question is NOT about correctness of the URL call. what do I put in the "body" and/or "Type", so to get the packages added. ? I can't find any documentation. I can and know how to use FLatten transformation if I need to flatten it afterwards

What do I need to put into the "Type" in the picture, so I can get the

score 0 · Answer 1 · answered May 10 '23 at 10:13

0

Click on the import projection in Output tab of external call transformation.
ADF will automatically detect the schema of the API call and Body type expression will be auto filled.

enter image description here

You can also manually specify the body data structure in type.

syntax: ( column1 as datatype, column2 as datatype.....)

Replace column1, column2 of above expression with actual column names and give the respective datatypes there.

sample expression: (updateTime as string, updated as string, validTimes as string)

answered May 10 '23 at 10:13

Aswin

4,090
2
4
16

Thank you. The "Import projections" does not return anything to me. That is why asked the question in the first place. Does it mean that it could not interpret the (external) rest call correctly ? Also, when specifying the body data structure in type MANUALLY, what are the correct datatypes? Am I following the same types as in sql? For example, what is the way to specify money ? double? float ? What are the ways to specify nested json? This is a type of documentation, I could not find. – Dmitriy Ryabin May 10 '23 at 13:51
Also, if my Linked Service (for external call) contains header setting for Authorization, I assume I don't need to provide that in the External Call transformation. Please, correct me if this is wrong. – Dmitriy Ryabin May 10 '23 at 13:52
Refer this [MS document](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-external-call#output). There is an example for body structure definition. It has nested json also. – Aswin May 15 '23 at 04:56

Azure data factory - Data flow External call - specify response body schema

1 Answers1