Data factory - ForEach Activity error - Next on empty iterator

Question

Data factory - ForEach Activity error - Next on empty iterator

Demarbre Sebastien 96

Hello ,

I have created a pipeline with two nested foreach activities.
The first one is extracting a list of files to load.
The second loop is iterating through each file to join them together.

In other words, I have a object with two attribute : mainTable (which is a string) and dependencies (which is an array of string).
In the second foreach, I'm iterating through all the names in the dependencies array , loading the corresponding file and joining it with the mainTable file.

When my dependencies array contains only one object, it is working well.
However, it is not working when the array contains more than 1 item. I've done five test with different objects names in the dependencies array and each time, I have the error "Next on empty iterator".

Does anyone know how to fix that issue ?

Thank you

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-06-30T05:33:05.96+00:00

Hi @Demarbre Sebastien ,

Thank you for posting your query on Microsoft Q&A.

Nested ForEach Activities(Inside Foreact, Another Foreach) is not supported in Azure data factory at this moment. Kindly check below link for same.
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity#limitations-and-workarounds

It would be great if you can share detailed flow of implementation(If possible along with snippets), So that we can try repro issue and help you in detailed resolution. Thank you.
Demarbre Sebastien 96 Reputation points

2021-06-30T06:55:57.043+00:00

Thank you @ShaikMaheer-MSFT for the provided link. However, I already read that nested foreach weren't supported for now and I use the following workaround : I created a second pipeline and use the "Run pipeline activity" to launch the second foreach.
The list on which the second foreach must iterate is stored in the pipeline parameters. I've attached the pipeline json but here is a short summary of the pipeline :

T1st step is a dataflow which is comparing columnNames of mainTable and dependency to identify which one are common.
2nd step is storing the common columnNames in a variable.
3rd step is a data flow making the join between main and dependency based on the column extracted in step 1.
The issue occurred after the first step : step is successful but pipeline stops because I receive the error mentioned (next on empty iterator). Nevertheless, it doesn't occur when "dependencies" array contains only one item.
110533-pipeline.txt
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-06-30T07:34:34.497+00:00
Hi @Demarbre Sebastien ,

Thank you for some insights. But still its not clear to re-pro scenario.

You mean if "dependencies" array parameter has multiple item, then on second iteration you are facing issue? If yes, Which activity exactly giving error? It will be great is you can take screenshot of entire error and attach it.

Below expression in Set variable getting giving you error in second iteration? If yes, Kindly check output json of step1 dataflow activity and see may be you are getting empty array in value property
"@activity('Extract PK Columns').output.runStatus.output.sinkJoinColumns.value[0].PKColumns"

Sample data which you passed in your pipeline parameters will also help in understanding a bit.

Kindly try to run pipeline and take screenshot where exactly we are getting error. I mean in which activity and share entire error screenshot or message.
Demarbre Sebastien 96 Reputation points

2021-06-30T08:41:30.34+00:00
Hi @ShaikMaheer-MSFT ,

The error occurs in the first step , the Extract Pk columns dataflow. However, if we have a look at the runs details, you can notice that all the steps in that activity are successful

Run summary

Error detail

1st iteration input

2nd iteration input

1st iteration output
110489-output-first-iteration.txt

2nd iteration output
110490-output-second-iteration.txt

I think it could help, here are the values of pipeline parameters :

Do you need more information ?
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-07-02T05:39:15.057+00:00

Hi @Demarbre Sebastien ,

Thank you for details. It seems your second value in array "vwDimRegulator" causing some issue internally while running dataflow.

Best way to check the root cause is, hardcode "vwDimRegulator" inside your dataflow parameters and try to perform data preview in each and every transformation step manually. By doing this we can come to exactly at which transformation your data flow is failing along with clear idea what might be causing this issue.

Could you please try same and let us know where exactly your data flow breaking. Thank you.

Accepted answer

0 additional answers

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-06-30T05:33:05.96+00:00

Hi @Demarbre Sebastien ,

Thank you for posting your query on Microsoft Q&A.

Nested ForEach Activities(Inside Foreact, Another Foreach) is not supported in Azure data factory at this moment. Kindly check below link for same.
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity#limitations-and-workarounds

It would be great if you can share detailed flow of implementation(If possible along with snippets), So that we can try repro issue and help you in detailed resolution. Thank you.
Demarbre Sebastien 96 Reputation points

2021-06-30T06:55:57.043+00:00

Thank you @ShaikMaheer-MSFT for the provided link. However, I already read that nested foreach weren't supported for now and I use the following workaround : I created a second pipeline and use the "Run pipeline activity" to launch the second foreach.
The list on which the second foreach must iterate is stored in the pipeline parameters. I've attached the pipeline json but here is a short summary of the pipeline :

T1st step is a dataflow which is comparing columnNames of mainTable and dependency to identify which one are common.
2nd step is storing the common columnNames in a variable.
3rd step is a data flow making the join between main and dependency based on the column extracted in step 1.
The issue occurred after the first step : step is successful but pipeline stops because I receive the error mentioned (next on empty iterator). Nevertheless, it doesn't occur when "dependencies" array contains only one item.
110533-pipeline.txt
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-06-30T07:34:34.497+00:00

Hi @Demarbre Sebastien ,

Thank you for some insights. But still its not clear to re-pro scenario.

You mean if "dependencies" array parameter has multiple item, then on second iteration you are facing issue? If yes, Which activity exactly giving error? It will be great is you can take screenshot of entire error and attach it.

Below expression in Set variable getting giving you error in second iteration? If yes, Kindly check output json of step1 dataflow activity and see may be you are getting empty array in value property
"@activity('Extract PK Columns').output.runStatus.output.sinkJoinColumns.value[0].PKColumns"

Sample data which you passed in your pipeline parameters will also help in understanding a bit.

Kindly try to run pipeline and take screenshot where exactly we are getting error. I mean in which activity and share entire error screenshot or message.
Demarbre Sebastien 96 Reputation points

2021-06-30T08:41:30.34+00:00

Hi @ShaikMaheer-MSFT ,

The error occurs in the first step , the Extract Pk columns dataflow. However, if we have a look at the runs details, you can notice that all the steps in that activity are successful

Run summary

Error detail

1st iteration input

2nd iteration input

1st iteration output
110489-output-first-iteration.txt

2nd iteration output
110490-output-second-iteration.txt

I think it could help, here are the values of pipeline parameters :

Do you need more information ?
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-07-02T05:39:15.057+00:00

Hi @Demarbre Sebastien ,

Thank you for details. It seems your second value in array "vwDimRegulator" causing some issue internally while running dataflow.

Best way to check the root cause is, hardcode "vwDimRegulator" inside your dataflow parameters and try to perform data preview in each and every transformation step manually. By doing this we can come to exactly at which transformation your data flow is failing along with clear idea what might be causing this issue.

Could you please try same and let us know where exactly your data flow breaking. Thank you.

Answer 1

Demarbre Sebastien 96

Hello @ShaikMaheer-MSFT ,

I've managed to fix the issue yesterday evening.
Apparently it was linked to the source file encoding. I think some of the files were in Unicode, some were uin UTF-8. Now, everything is in UTF-8 and the pipeline ran successfully.

Thus, not really linked to the iterator. I think I was just lucky with all my previous tests , I selected only files with right encoding.

Thanks for your help.

Sebastien

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-07-02T07:24:47.75+00:00

Hello @Demarbre Sebastien ,

Happy to know that your issue resolved. Kindly mark above answer as accepted. That will help community also. Thank you.

Share via

Data factory - ForEach Activity error - Next on empty iterator

0 additional answers

Your answer