Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am using a Data Flow activity in Azure Data Factory to flatten a JSON file contianing Workspace Ids and info and retrieve only the WorkspaceId from the JSON array. The sink caches the output.
The beginning of the JSON output of the dataflow looks like this:
And the part I want to iterate over in the ForEach loop is here, the WorkspaceIds:
I want to use this output in a ForEach. I have watched a bunch of tutorials and read a ton of blogs of what to write in the Settings>Items section of the ForEach and none of the options work.
If I use this:
I get this error: The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.
Alright, I understand that the JSON file is an 'object' and I need to point to the array within. So I tried these next:
I have tried every combo of the path from the JSON. Including runStatus.output.sink1.value.WorkspaceId and all combos in between, including and removing certain levels and so on.
I get various renditions of this error, depending on what I write in the expression builder:
The expression 'length(activity('DataFlowWorkspaceIdArray').output.value)' cannot be evaluated because property 'value' doesn't exist, available properties are 'runStatus, effectiveIntegrationRuntime, billingReference, reportLineageToPurview'.
Please, let me know how I can fix this error. Is this a data flow problem? Should I be using a different activity to remove the WorkspaceId from the original JSON file? Am I writing the wrong code in the expression builder? Any help is appreciated. Thank you.
Solved! Go to Solution.
@Anonymous helped me fix this issue by completely rethinking my pipeline.
Instead of using a dataflow to flatten the JSON, I'm using a Web Activity instead of the Copy Activity to call the RestAPI. Then I created an Array variable for the pipeline and used the Set Variable activity to make the output of the Web Activity into an array. Now the ForEach can read the JSON output.
Hi @Neka ,
Thanks for using Fabric Community.
Based on the Json Structure -
@activity('DataFlowWorkspaceIdArray').output.sink1.value
Can you please try this?
Hello, thank you for your response. When I use that I get this error:
The expression 'length(activity('DataFlowWorkspaceIdArray').output.sink1.value)' cannot be evaluated because property 'sink1' doesn't exist, available properties are 'runStatus, effectiveIntegrationRuntime, billingReference, reportLineageToPurview'.
If I include 'runStatus' and change it to:
Hi @Neka ,
Can you please share the complete Json Output, so I can get an idea on the structure?
If possible can you also send complete data pipeline screenshot.
Hello,
This is what the JSON output looks like. I have removed identifying features and taken out the full list of workspaces:
"runStatus": {
"ClusterId": "dfXXX",
"sparkVersion": "3.1",
"computeAcquisitionDuration": 116025,
"version": "20240304.2",
"profile": {
"source1": {
"computed": [],
"lineage": {},
"dropped": 0,
"drifted": 0,
"newer": 3,
"total": 3,
"updated": 0
},
"flatten1": {
"computed": [],
"lineage": {},
"dropped": 3,
"drifted": 0,
"newer": 1,
"total": 1,
"updated": 0
},
"sink1": {
"computed": [],
"lineage": {
"WorkspaceId": {
"mapped": false,
"from": [
{
"source": "source1",
"columns": [
"value"
]
}
]
}
},
"dropped": 0,
"drifted": 0,
"newer": 0,
"total": 1,
"updated": 1
}
},
"metrics": {
"sink1": {
"format": "",
"stages": [
{
"stage": 12,
"partitionTimes": [],
"recordsWritten": 0,
"lastUpdateTime": "2024-03-13 19:27:18.989",
"bytesWritten": 0,
"recordsRead": 1,
"bytesRead": 467417,
"partitionStatus": "Success",
"streams": {},
"target": "sink1",
"time": 192,
"progressState": "Completed"
}
],
"sinkPostProcessingTime": 0,
"store": "",
"rowsWritten": 0,
"details": {},
"progressState": "Completed",
"sources": {},
"sinkProcessingTime": 320
}
},
"clusterComputeId": "fXXX",
"output": {
"sink1": {
"value": [
{
"WorkspaceId": "15XXX"
},
{
"WorkspaceId": "bcXXX"
},
{
"WorkspaceId": "d9XXX"
},
{
"WorkspaceId": "7eXXX"
}
],
"count": 1786
}
},
"dsl": "\nsource() ~> source1\n\nsource1 foldDown() ~> flatten1\n\nflatten1 sink() ~> sink1"
},
"effectiveIntegrationRuntime": "debugpool-8Cores-General-13 (East US 2)",
"billingReference": {
"activityType": "executedataflow",
"billableDuration": [
{
"meterType": "General",
"duration": 0.273322454,
"unit": "coreHour",
"sessionType": "JobCluster"
}
]
},
"reportLineageToPurview": {
"status": "NotReported"
}
}
Here is a screenshot, of the data pipeline:
Here is the dataflow within the pipeline:
Hi @Neka ,
When I closely observe your Json Response,
@activity('DataFlowWorkspaceIdArray').runStatus.output.sink1.value
The above code will return this list -
Can you please try and let me know the output?
Hello,
If I put that statement in the ForEach activity settings and run it, I get this error:
The expression 'length(activity('DataFlowWorkspaceIdArray').runStatus.output.sink1.value)' cannot be evaluated because property 'runStatus' doesn't exist, available properties are 'PipelineName, PipelineRunId, JobId, ActivityRunId, ExecutionStartTime, ExecutionEndTime, Status, Error, Output, ExecutionDetails, StatusCode, ExecutionStatus, Duration, RecoveryStatus, ActivityType'.
Hi @Neka ,
Can you please help me understand in which activity you have written above expression?
In order to help you better, I would like to from which activity you got the above json as response and in which activity you are consuming it?
Based on the screenshot I assume you are simply using dataflow gen 2 flatten the json.
Hi ,
Can you please help me understand in which activity you have written above expression?
In order to help you better, I would like to from which activity you got the above json as response and in which activity you are consuming it?
Based on the screenshot I assume you are simply using dataflow gen 2 flatten the json.
I suggest you can replace the Copy Data Activity with Web Activity and connect it to ForEach Acitivity.
And then you can use this expression -
@activity('DataFlowWorkspaceIdArray').runStatus.output.sink1.value
Hope this is helpful. Please let me know incase of further queries.
@Anonymous helped me fix this issue by completely rethinking my pipeline.
Instead of using a dataflow to flatten the JSON, I'm using a Web Activity instead of the Copy Activity to call the RestAPI. Then I created an Array variable for the pipeline and used the Set Variable activity to make the output of the Web Activity into an array. Now the ForEach can read the JSON output.
Hi @Neka ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .
Hi @Neka ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .
User | Count |
---|---|
6 | |
6 | |
4 | |
3 | |
3 |
User | Count |
---|---|
14 | |
10 | |
7 | |
7 | |
6 |