Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
With Azure Synapse Analytics Copy Activity I use pagination to combine multiple REST calls into one JSON file.
Jürg Gubler
0
Reputation points
With Azure Synapse Analytics Copy Activity I use pagination to combine multiple REST calls into one JSON file.
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?
Azure Synapse Analytics
{count} votes
-
Vinodh247 40,141 Reputation points MVP Volunteer Moderator2025-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.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 65%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECB%3C/text%3E%3C/svg%3E)
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator2025-01-10T20:07:47.3266667+00:00 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 15,455 Reputation points Microsoft External Staff Moderator
2025-01-13T06:01:25.1733333+00:00 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.
Sign in to comment
Sign in to answer