Replicating SQL IN operator in Azure Data Factory


(2020-May-01) There is a current limitation in Azure Data Factory (ADF) to accept only two conditions for the OR function.

You won't be able to specify the following expression to evaluate 3 possible values for a variable:
or(equals(variables('var1'), 'A'), equals(variables('var1'), 'B'), equals(variables('var1'), 'C')) - not possible.

However only this use-case for the OR function with 2 condition could be possible:

or(equals(variables('var1'), 'A'), equals(variables('var1'), 'B')) - limit of two conditions

But what if we have an ability to check if a particular element variable/parameter/other ADF object value belongs to a range of values (array of value), similarly to what we can do with the IN operator in SQL language, this would definitely solve our problem and remove the limitation of logical conditions to check.

The previous attempted OR expression with 3 conditions can be successfully written this way:
contains(createArray('A', 'B', 'C'),variables('var1'))

An array of values in your real-life ADF pipelines could come as an output of other previous activities or it can be a hardcoded list of values that you would want your variables to evaluate.

A similar SQL IN construction:

Could be created in Azure Data Factory this way:

Simple ADF tip to share! 🙂

Original post (opens in new tab)