The ForEach activity is a great addition to Azure Data Factory v2 (ADF v2) – however, you can encounter issues in some situations where you pass a null in it’s ‘Items’ setting for it to iterate.
When you pass a Null, you receive the error:
Error
{
"errorCode": "InvalidTemplate",
"message": "The function 'length' expects its parameter to be an array or a string. The provided value IS of type 'Null'.",
"failureType": "UserError",
"target": "ForEach"
}
This happens because the initialisation of the ForEach iterator checks the length of the string or array that is passed in on the ‘Items’ setting. When a null is supplied (i.e. no items to create an array from), the length function fails. I would like to see the ADF ForEach Activity check for null first and only check the length and continue with the iterator when it’s not null but it doesn’t, although I’m sure that will tighten up in future versions.
Arguably the correct way to handle this is to implement an IF condition operator activity within your pipeline that tests for Null and only execute the ForEach iterator activities when you can confirm the object you want to iterate is not null. However, for me that slightly overcomplicates the pipeline as you end up with nested activities / or additional pipelines that make it difficult to maintain and really understand what’s happening in the pipeline.
There’s an alternative and I’d be interested in understanding if there are any better alternatives to achieve the same result. Please comment below.
Rather than passing the Null object, we can run an inline test within the Items attribute and pass either a valid object for iterating (when the object is not null) or pass it an empty array when the object is null. It took me a while to work out how to create an empty array. @array(”) returned an array with a length of 1 so the ForEach loop fired but then subsequently failed as there was nothing to grab from the array. An empty string had the same effect too. We need to generate an array (or a string) with a length of zero.
The solution was to ‘take‘ an item from array(”) – which item? The item with index of 0. This returned me a non null array with a length of 0 – the ForEach loop now didn’t fail, it now succeeded and better still, didn’t trigger the sub activities – the result I wanted.
Wrapping this into a coalesce provides a self contained defensive null handling pattern that should arguably always be used on the Items setting of the ForEach activity.
@coalesce(<##Your string or array to iterate that might be null##>,take(array(''),0))
I would love to hear better/alternative approaches that don’t rely on the IF conditional activity.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr