With Azure Synapse Analytics Copy Activity I use pagination to combine multiple REST calls into one JSON file.

Jürg Gubler 0

The resulting data however is a combination of several JSON structures and itself not a valid JSON format. How can I combine the multiple REST call results as one JSON structure into one or multiple files within the Copy activity?

Vinodh247 27,281 Reputation points MVP

2025-01-09T16:35:26.3366667+00:00
To combine multiple REST call results into a valid JSON structure within synapse copy activity, you can try the below steps:

Enable Pagination in Copy Activity

In the copy activity, configure the source REST connector with pagination rules.

Use a supported pagination mechanism such as:

AbsoluteURL: Uses a specific URL for the next page.

NextRequestHeader: Extracts pagination tokens from the response header.

ContinuationToken: Uses a token or offset to request the next page.

Ensure all the results are fetched and combined into a single dataset by enabling pagination correctly.

Configure Data Mapping

Define a mapping schema in the Copy Activity to standardize the fields from the paginated responses.

Map fields into a flat structure or JSON hierarchy to align all responses into a consistent structure.

Write to a Single JSON File

Configure the Sink (destination) in the Copy Activity:

Set the sink type to JSON.

Enable "Merge Files" if writing to a file-based sink like Azure Blob Storage or ADLSGen2. This ensures all the paginated responses are combined into a single output file.

If the JSON structure isn't valid, enable schema transformation.

Transform Data with Data Flow (Optional)

If the raw REST responses have inconsistent JSON structures, you can use Mapping Data Flows in Synapse.

Add a Derived Column transformation to unify the JSON structure.

Use a Flatten transformation if nested JSON elements need normalization.

Write the transformed data to a JSON file sink.

Use Script Activity for Custom Processing (Optional)

If the JSON responses cannot be combined into a valid structure using Copy Activity alone:

Copy the raw results into a temporary storage (a staging blob).

Use a Script Activity with Python or Spark to process the raw data and generate a valid JSON file.

Save the processed JSON file back to the desired storage.

Validate Output

Ensure the final JSON file is valid by loading it into a JSON validator or a downstream system.

If needed, test the file format with a small subset of the data.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-10T20:07:47.3266667+00:00

@Jürg Gubler

Just checking in to see if the above suggestion provided by @ Vinodh247 helped and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-13T06:01:25.1733333+00:00

@Jürg Gubler

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

With Azure Synapse Analytics Copy Activity I use pagination to combine multiple REST calls into one JSON file.

Your answer