topic Re: Copy Data Activity for JSON from paged API response in Pipelines

Copy Data Activity for JSON from paged API response

mark_carlisle — Fri, 10 May 2024 17:40:39 GMT

I have setup a data pipeline that successful retrives data from an API and loads the JSON response into a lakehouse.

My issue is how the copy data activity saves a paged response to a single JSON file, like this:

{ "results": [REMOVED], "resultsTotal": 255, "resultsPage": 0, "resultsPageSize": 100, "nextCursor": "AQ=AA=AYrsQAgw=MTk5OTIzMDYyNyE1M2E0NWRjYjNhNDUyMjc1NGE5MjgwOTY4NWFiOTMwMw", "startDate": "2023-10-01T00:00:00.000+0000", "endDate": "2023-10-02T00:00:00.000+0000" } { "results": [REMOVED], "resultsTotal": 255, "resultsPage": 1, "resultsPageSize": 100, "prevCursor": "AQ=AQ=AYrsP7JA=MTk5OTIzMDYyNyE0ZjhhYmY1N2U2MzM5YTFiYjA5OWNhZjE4ZTY4NDdlNA", "nextCursor": "AQ=AA=AYrqPFiQ=MTk5OTIzMDYyNyFjZjFjZmIyN2E1NWExYTk1NDJiYWYyMmIxNzczNjJlNg", "startDate": "2023-10-01T00:00:00.000+0000", "endDate": "2023-10-02T00:00:00.000+0000" } { "results": [REMOVED], "resultsTotal": 255, "resultsPage": 2, "resultsPageSize": 100, "prevCursor": "AQ=AQ=AYrqLxIg=MTk5OTIzMDYyNyFkYzNjNzczY2Q0Njg3Yjc1NTUzZGJhNjRiMzIwZDg3MQ", "startDate": "2023-10-01T00:00:00.000+0000", "endDate": "2023-10-02T00:00:00.000+0000" }

This is an example of a response that required paging and what Fabric loaded into the Lakehouse, note the lack of commas between the response, invalidating the JSON.

Is this a limitation of the activity or have I got it configured incorrectly?

Re: Copy Data Activity for JSON from paged API response

Nero — Sun, 12 May 2024 20:32:46 GMT

Try setting File Pattern to "Array of Objects"

Re: Copy Data Activity for JSON from paged API response

mark_carlisle — Mon, 13 May 2024 08:15:29 GMT

Hi,

Thanks for your reponse. Managed to figure this out over the weekend. Looks like I'm impacted by a bug of some sort because we'd been switching that setting about and still getting the same output. It would seem that this bug is preventing the setting taking effect:

I was able to overcome this by editing the JSON (Edit Fabric Pipeline's JSON (linkedin.com)) manually.

Re: Copy Data Activity for JSON from paged API response

v-cboorla-msft — Mon, 13 May 2024 08:56:51 GMT

Hi mark_carlisle

Thanks for using Microsoft Fabric Community.

Glad that you were able to find some insights and thank you for sharing the same with the community as it can be helpful to others.
Please continue using Fabric Community for further queries.

Thank you.