Pipeline Copy Activity - JSON Source With Dynamic Schema

Question

Greetings,

Looking for recommendations on how you would solve the following problem in an Azure Synapse Analytics Pipeline.

I have a child pipeline that looks like the following. This pipeline fetches a series of "groups" from the source system, iterates over that group via a For Each and calls a Copy activity. The source of the Copy activity is a rest component that returns JSON content. The API endpoint in question returns 50 records per request, which is not configurable other than the ability to specify an offset argument to iterate through the paginated results and this is where I am experiencing issues.

Depending on how many records are in the response, the JSON will either return a singular JSON object or an array of objects. The Mapping on the copy activity is defined below, and as you will see, is expecting an array of objects. When a request produces a response with a single JSON object, the inner copy activity of the For Each fails, therefore, failing that step.

How does one handle dynamic JSON results where the format of the schema changes depending on the number of results in a response?

Pipeline:

Pagination Documentation:

Mapping:

Sample JSON:

Answer 1

Hello @28026517

Handling dynamic JSON results with varying schema based on the number of results can be challenging.

In Mapping Data Flows, Did you try the "Derived Column" transformation to create a new column that contains the dynamic schema.

I have tested this scenario by loading the input source JSON node (i.e Status) into a Derived column (In DataFlow)

I have used the Derived Column to read the json node called 'Status' as a new column.

Below is the output for 2 scenarios. Later i will make use of these in further processing!