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

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

Reply
Neka
Helper II
Helper II

Output from Data Flow can't be used in ForEach due to 'length' or 'value' doesn't exist error

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. 

Neka_0-1710452752981.png

 

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:

Neka_2-1710452752808.png

 

 

 

 

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:

Neka_2-1710508825541.png

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:

 

Neka_1-1710508798746.png

 

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.

1 ACCEPTED 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. 

 

Neka_0-1711549894317.png

 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi @Neka ,

Thanks for using Fabric Community.
Based on the Json Structure - 

vgchennamsft_0-1710737467004.png

 

@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: 

@activity('DataFlowWorkspaceIdArray').runStatus.output.sink1.value
 
then 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'.
Anonymous
Not applicable

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:

Neka_0-1711389762448.png

Here is the dataflow within the pipeline:

Neka_1-1711389815389.png

 

Anonymous
Not applicable

Hi @Neka ,

When I closely observe your Json Response,

vgchennamsft_0-1711464078757.png

 

@activity('DataFlowWorkspaceIdArray').runStatus.output.sink1.value

 

The above code will return this list -

vgchennamsft_2-1711464218484.png

 

 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'.

Anonymous
Not applicable

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.

vgchennamsft_0-1711466573654.png


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. 

 

Neka_0-1711549894317.png

 

Anonymous
Not applicable

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 .

Anonymous
Not applicable

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 .

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.