Copy Data Activity for JSON from paged API response

[mark_carlisle]

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?

[Nero · Accepted solution]

Try setting File Pattern to "Array of Objects"

[mark_carlisle]

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.

[v-cboorla-msft]

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.