Error ingesting json files in warehouse table

Hi smeetsh, 

The error "Not able to get enumerator for non-array. Path: databody.daysQuality.testResults" usually means the mapping expects an array at that JSON path, but at least one incoming file has a null, a single object, or some other non-array value there. In other words, the source shape changed and the Copy activity can no longer iterate it (inference based on how Fabric parses hierarchical JSON).

I'd suggest trying to flatten the column and treat it as a string, that way it will load nicely regardless if it is null or not.

• Open the Copy activity > Mappings > expand Column flatten settings and, just for the problematic column, enable Treat array as string or Treat struct as string. This bypasses enumeration and lands the raw value as text that you can parse downstream. See the official mapping options for arrays/structs in Fabric’s Copy activity. 

For the skip incompatible rows setting, this is for row-level incompatibilities between source rows and the sink schema (type mismatches, PK violations, etc.). It can keep a load going and log skipped rows, but it won’t fix a fundamental JSON shape mismatch that throws earlier during parsing. Use it only if you can safely ignore some bad rows and review the session log. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

Hi smeetsh,

Thank you for reaching out to the Microsoft Fabric Community Forum and providing the details and the JSON examples.

The error you are seeing is happening because the testResults field is sometimes an array (with values) and sometimes null. The pipeline expects it to always be an array, so when it encounters null, it fails with the “Not able to get enumerator for non-array” error. This wasn’t enforced as strictly before, which is why it may have only started breaking recently.

To fix this, the recommended approach is to normalize the JSON before ingestion so that null values are replaced with empty arrays []. This keeps the schema consistent and prevents the pipeline from failing. https://learn.microsoft.com/en-in/azure/data-factory/copy-activity-schema-and-type-mapping 

As a quick workaround, you can also enable Skip incompatible rows under Copy Activity → Settings → Fault tolerance but do note that this will drop any rows where testResults is null.
https://learn.microsoft.com/en-in/azure/data-factory/copy-activity-fault-tolerance 

For a long-term stable solution, I would suggest going with the normalization option, so you don’t risk losing records when the source sends null.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

A small update on this.  It is not resolved yet. I have tried skipping incompatible rows, since our data is built in such a way that that wouldn't case an issue. But for some reason came up with the same error. Flattening all arrays to strings I havent tried yet, since this would cause havoc with our ETL in the next step.

Does anyone have a good trick to change the empty arrays to [] I have been looking at doing this with a notebook, but I wasn't able to get that working.

Hi All,

A small update, The data engineering team is now trying to schedule a teams meeting with me, so it is still in the hands of MicroSoft.

I think, in de mean time, I have a working notebook, but the bussines analyst still needs to verify the data.

If at all possible, I want the old solution to start working again, the notebook way seems very convoluted and the fact Fabric was great in dealing with jsons in the past, using a notebook feels as a step back (Granted me not being very versed in SparkSQL doesn't help either, I had a lot of help from AI to get it to work, and it always bugs me if I dont fully understand what I have written...SparkSQL turns out to be quite different from SQL, once you get into more complex fucntions) 

Some snippets of the code below, especially having to define each datatype for each key is a lot of work which is prone to error, and as i said we have several nested arrays getting returned from the API. I have removed quite a few lines of code from the sample below. Maybe the python route is easier, I don't know? 

==============

Good morning All,

A small update, which is not really and update, but MS came back indicating they need more time and that the issue encountered seems to be more complex than expected.

v-kpoloju-msft We are already writing the data to a raw json file, this allowed us to identify the issue quick.

I will be looking into python as a notebook, as soon as i get a chance.

It is the copy activity to the lakehouse that is actaully throwing the error, preventing us from loading like we used to (this pipeline has worked fine for a least a year). The only thing we were able to do was change the 1st copy step to flatten the whole json array into one single column and next use a notebook.

Cheers

Hans

Another small update, but sadly not with a resolution yet. Yesterday I got another email from them, asking me for some more patience. A number of microft internal teams  is working on it and the are finalizing the next steps. 

It realy feels like a change was made with unintended consequences, well I hope at least, or else I will have my work cut out for me 😁

Hi All,

Just a further update , that is not really an update. Due to the amount of time it is taking something that wasn't urgen,  now is becomming urgent and I may have no option than to tackle one of our biggest and most datasets and add in some form or python notebook, that can take a json array, that now needs to be loaded in a single column and flatten it into its respective columns like it just to be.

MS assured me Friday, after i pointed this out and asked for an escalation, it is already escalated to the "Product Group Team" . However I wont have time to wait months for them to figure this out.

This change will be massive, since the dataset goes back over 10 years and data can not be retrieved again from the source if i get this wrong . So it means backup plans, change requests, risk etc.😥

Good morning All, sadly another update that isn't an  update. I tried some suggestion of MS last week, which didn't work. They wanted me to manually edit the json of the pipeling to make the column type "Array", which is an option that is not in the GUI, but apparently you can edit the json code. The error was still the same.

Does anyone have an escalation email address of someking, becuause (just like with the lakehouse sql endpoint delay) I am getting the feeling this is not going to get fixed anytime soon. For the sqlendpoint delay user now have to fix that themselfs. 

Cheers

Hans

Thanks for the suggestion, I'll try it out. Question though. I have found json files from 6 months ago, which also had this issue, and it didnt cause a problem. Has the way json is handled in a copy activity changed in the last update?

The error has slightly changed: it now says "databody.daysQuality[14].testResults", in stead of 

"Path: databody.daysQuality.testResults"  (there is a [14] added to it now, is that an idication of a specific record)

ErrorCode=UserErrorWriteFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: '9e9fce10-9b68-486f-8d48-b77f907bba71/_system/services/DI/pipelines/624f7dd3-e46f-4c8a-9c22-64f824c713f0/MSSQLImportCommand/33172.parquet'.,Source=Microsoft.DataTransfer.Common,''Type=System.ArgumentException,Message=Node is not JArray. path: databody.daysQuality[14].testResults, type Null,Source=Microsoft.DataTransfer.Common,'