loading each object in an array vs the page

Question

Greetings,

I am just now getting started with Data Factory and am a little confused. Below is a sample of the JSON from the REST API that I am pulling. I have the pipeline working to paginate and save blobs, but it only saves each page and not each results object. So I would get 2 files in this case instead of what I want which would be 970 json event objects that are named with the number used in the id attribute (750695.json and 750696.json using the example objects below). Isn't that the purpose of the mapping tab and doing all of that schema mapping? I am not clear why my pipeline is only saving pages to my container and not objects.

This appears to be straightforward using all of the documentation. I have used Alteryx and Tableau Prep before, so I am familiar with the concepts. I just can't make it work in Data Factory. I am way out in left field?

I definitely am not asking anyone to solve this for me....if you could offer some hints on what to look at to make this happen, I would greatly appreciate.

"page": 1,
"pageSize": 500,
"pageCount": 2,
"resultsCount": 970,
"results": [
{
"id": 750695,
"eventName": "Class",
"eventTypeId": 34,
"eventTypeDisplayOnWeb": true,
"reserveStartTime": "2022-01-24T11:00:00-05:00",
"reserveEndTime": "2022-01-24T12:20:00-05:00",
"eventStartTime": "2022-01-24T11:00:00-05:00",
"eventEndTime": "2022-01-24T12:20:00-05:00",
"outlookPatternOriginalDateTime": null,
"tearDownMinutes": 0,
"setupMinutes": 0,
"setupTypeId": 6,
"setupCount": 40,
"actualAttendance": 0,
"reservation": {
"id": 103857,
"webUserId": 0,
"templateId": 0,
"groupName": "Registrar",
"groupDisplayOnWeb": true,
"vip": false,
"eventName": ""
},
"status": {
"id": 1,
"description": "Academic Confirmed",
"statusType": "BookedSpace",
"displayOnWeb": true,
"allowBookingEdit": false,
"allowServiceEdit": false,
"allowBookingCancellation": false,
"commitInventory": false
},
"group": {
"id": 1,
"name": "Registrar",
"emailAddress": ""
},
"room": {
"id": 3,
"requiresCheckIn": false,
"promptForBillingReference": false,
"displayOnWeb": true,
"imageId": 0,
"recordType": "NotSet",
"roomType": {
"id": 299,
"description": "Classroom - A",
"sequence": 0
},
"floor": {
"id": 66,
"description": "00 - Basement Floor",
"sequence": 0
},
"building": {
"id": 4,
"timezoneId": 61,
"timeZone": {
"id": 61,
"name": "Eastern Time",
"abbreviation": "ET",
"description": "Eastern Standard Time",
"gmtDescription": "(GMT -05:00)",
"hourOffset": -5,
"minuteOffset": -300,
"timestamp": null
},
"notes": "",
"url": null,
"currency": null,
"calendaringTBDRoom": null
},
"defaultSetupTypeId": 0,
"defaultCapacity": null,
"isAssociatedRoom": false,
"hideGroup": false
},
"videoConference": false,
"isHost": false,
"pamId": "",
"calendarUID": "",
"conferenceProvider": "",
"conferenceCode": null,
"conferenceURL": null,
"conferenceHostURL": null,
"canEdit": false,
"canEndNow": false,
"canCancel": false,
"checkedIn": false,
"canCheckIn": false,
"conflictResolutionGroup": null,
"overrideDescription": null,
"changeCounter": 0,
"hasServiceOrders": false,
"maxOccupancy": null,
"avgOccupancy": null
},
{
"id": 750696,
"eventName": "ClassName",
"eventTypeId": 34,
"eventTypeDisplayOnWeb": true,
"reserveStartTime": "2022-01-26T11:00:00-05:00",
"reserveEndTime": "2022-01-26T12:20:00-05:00",
"eventStartTime": "2022-01-26T11:00:00-05:00",
"eventEndTime": "2022-01-26T12:20:00-05:00",
"outlookPatternOriginalDateTime": null,
"tearDownMinutes": 0,
"setupMinutes": 0,
"setupTypeId": 6,
"setupCount": 40,
"actualAttendance": 0,
"reservation": {
"id": 103857,
"webUserId": 0,
"templateId": 0,
"groupName": "Registrar",
"groupDisplayOnWeb": true,
"contactName": "Wilson,Andrea",
"vip": false,
"eventName": ""
},
"status": {
"id": 1,
"description": "Academic Confirmed",
"statusType": "BookedSpace",
"displayOnWeb": true,
"allowBookingEdit": false,
"allowServiceEdit": false,
"allowBookingCancellation": false,
"commitInventory": false
},
"group": {
"id": 1,
"name": "Registrar",
"emailAddress": ""
},
"room": {
"id": 3,
"code": "BLDGCODE",
"description": "028",
"requiresCheckIn": false,
"promptForBillingReference": false,
"displayOnWeb": true,
"imageId": 0,
"recordType": "NotSet",
"roomType": {
"id": 299,
"description": "Classroom - A",
"sequence": 0
},
"floor": {
"id": 66,
"description": "00 - Basement Floor",
"sequence": 0
},
"building": {
"id": 4,
"description": "BuildingName",
"code": "Bldgcode",
"timezoneId": 61,
"timeZone": {
"id": 61,
"name": "Eastern Time",
"abbreviation": "ET",
"description": "Eastern Standard Time",
"gmtDescription": "(GMT -05:00)",
"hourOffset": -5,
"minuteOffset": -300,
"timestamp": null
},
"notes": "",
"url": null,
"currency": null,
"calendaringTBDRoom": null
},
"defaultSetupTypeId": 0,
"defaultCapacity": null,
"isAssociatedRoom": false,
"hideGroup": false
},
"videoConference": false,
"isHost": false,
"pamId": "",
"calendarUID": "",
"conferenceProvider": "",
"conferenceCode": null,
"conferenceURL": null,
"conferenceHostURL": null,
"canEdit": false,
"canEndNow": false,
"canCancel": false,
"checkedIn": false,
"canCheckIn": false,
"conflictResolutionGroup": null,
"overrideDescription": null,
"changeCounter": 0,
"hasServiceOrders": false,
"audit": {
"addedBy": "JG",
"dateAdded": "2022-03-02T00:33:05.913Z",
"changedBy": "JG",
"dateChanged": "2022-03-02T00:33:05.913Z"
},
"maxOccupancy": null,
"avgOccupancy": null
},...

Answer 1

Hi @Joshua P. Thomas ,

Thank you for posting query in Microsoft Q&A Platform.

As I understand your query better, you tried Copy activity with REST dataset as source and pagination rules in it to load data to blob. you see that each page REST response saves as blob there and want to confirm is that the way it works. Please correct me if my understanding is wrong.

Yes each page response will save as blob in your case. ADF copy activity will take each page response and saves it to sink. Mapping tab in copy activity is use help you with mapping of columns between source and sink.

You can refer below useful links to know more about copy activity and REST Connector.

• Copy activity in Azure Data Factory and Azure Synapse Analytics
• Copy and transform data from and to a REST endpoint by using Azure Data Factory

If you would like to get control on each object of your json response then, use web activity to make REST API call and then pass your json response object array to ForEach activity to iterate over each object. Inside ForEach activity use copy activity.

In copy activity use a dummy file as source dataset and make use of adding additional columns on source to pass your json object key values as columns. Use mapping tab to map required columns from source to sink.

Please check below video to know about adding additional columns in copy activity.
https://www.youtube.com/watch?v=m0Zc-CofAuc

Below are few videos which explains about web activity & ForEach activity. Kindly feel free to check if that helps.

• Web Activity in Azure Data Factory
• For Each activity in Azure Data Factory

Hope this helps. Please let us know if any further queries.

---------------

Please consider hitting Accept Answer. Accepted answers helps community as well.