Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mark_carlisle
Advocate IV
Advocate IV

Copy Data Activity for JSON from paged API response

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?

mark_carlisle_0-1715362798130.png

 

1 ACCEPTED SOLUTION
Nero
Frequent Visitor

Try setting File Pattern to "Array of Objects"

Nero_0-1715545942525.png

 

View solution in original post

3 REPLIES 3
Nero
Frequent Visitor

Try setting File Pattern to "Array of Objects"

Nero_0-1715545942525.png

 

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:

mark_carlisle_0-1715587970410.png

 

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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.