Azure Dataflow - External Call | Dynamic Body Structure for Response

Question

There is not any documentation surrounding Dynamic Content to generate the body structure for REST responses. I am using a dataflow to dynamically make REST requests against different endpoints of the same service ie customers, orders, inventory, etc... so the responses that are returned have different objects meaning I cannot use a static body structure. I have tried something like this...

"( {$pathName} as ( {trim(reduce(map(cachedTypes#outputs(), #item.shop + " as " + #item.datatype), "", #acc + toString(#item) + ",", #result))} )[])"

but get syntax errors each time. The only dynamic reference on the documentation is the "{@context}" variable used in the body structure which seems to be a reference to an input column. Even then, when i Generate the body structure via a derived column activity prior to the external call and try to use the value from this to "pass" aka generate the body structure I am unable to figure out how to add this to the expression builder without throwing an error.

MS Documentation - External call transformation in mapping data flows

Answer 1

As of now you need to pass the schema definition of complex type.

Syntax

(<name> as <type>, <name> as <type>,<name> as <type>,...)

You cannot generate a dynamic expression using any function, only available things are

So, work around in your case is to use for each activity.

For every api use switch activity and call dataflow activity accordingly, but here you need to build dataflow activity for each api

OR

get the response as string and parse the body with combining all the response schema.

but here you need to give the schema again combination of all the response.

Example:

I have api endpoint movies and products

next, you give response as none

you will get output in string format like below.

Next you use them in further activity separately. If you try to parse them you need to give schema again, since the body contains different fields even if try to parse you will get some column null values.

New column parsed_data, expression is string type body and output column type is the schema i given that is combination of movies and products schemas.

You access them in following downstream like parsed_data.id,parsed_data.CPU..

Output: