Sacha Tomey

Sacha Tomey's Blog

Azure Data Factory v2 : ForEach Activity : Handling Null Items

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.

Loading