Parsing row counts from json output from an ADF script to pass to Stored Procedure

Question

I have a ADF script that simply performs the following from a ForEach Loop.

@markus.bohland@hotmail.de (concat(concat('select count(*) as num_rows from ', item().table_owner_schema), '.'), item().table_name)

This works fine and returns the row count in the json output. My dilemma is I cannot retrieve the value for num_rows from the output as shown below.

{
"resultSetCount": 1,
"recordsAffected": 0,
"resultSets": [
{
"rowCount": 1,
"rows": [
{
"NUM_ROWS": 4506
}
]
}
],
"outputParameters": {},
"outputLogs": "",
"outputLogsLocation": "",
"outputTruncated": false,
"effectiveIntegrationRuntime": "100weccp1at0001-IR",
"executionDuration": 0,
"durationInQueue": {
"integrationRuntimeQueue": 1
},
"billingReference": {
"activityType": "PipelineActivity",
"billableDuration": [
{
"meterType": "SelfhostedIR",
"duration": 0.016666666666666666,
"unit": "Hours"
}
]
}
}

I am trying to pass the num_rows as lastrowcounts in the SP. The other inputs work fine, but I cannot extract the num_rows.

Any thoughts would be greatly appreciated.

Answer 1

Hi @Haliburton, Russell ,

Thankyou for your question on Microsoft Q&A platform.

You have investigated correctly. Since NUM_ROWS property is within rows[] array which is within resultSets[] array, you need to use @activity('activityName').output.resultSets[0].rows[0].NUM_ROWS

For more details , kindly checkout this video: How to read JSON output of one Activity in to another Activity in Azure Data Factory

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Discovered what I was doing incorrectly. The lastrownum should have been @activity('Select RowCounts').output.resultSets[0].rows[0].NUM_ROWS

Thanks for investigating,