When you are working with ETL/ELT, sometimes you may need to transform NULL into something meaningful value. If you worked with SSIS, you know how you handle that. This blog post will describe how do we do in SSIS and how the very same task can be done in ADF Dataflow.
Consider, we have a .csv file where Name columns have NULL value for 2nd record (figure: 1.0)
After connecting the .csv file through flat file source in SSIS data flow, we can debug and view the record through data viewer which will look like below figure 1.1
If you would like to replace the NULL value with meaning value, in that case you need to use derive column activity and use expression.
SSIS data flow expression got REPLACENULL function, which will replace NULL to the expected value that you want.
The expression: REPLACENULL(Name,"Unknown")
The above expression will return 'Unknown' when Name is NULL otherwise it will return the original value.
When it comes to ADF data flow regular expression similar like SSIS expression; isNull only give you true or false. And isNull function take only one argument, e.g. below fig 2.1 took the argument Name and return True (✓) if the value is NULL.
Now, let's find out how to transform NULL value into something meaningful in ADF data flow. ADF doesn’t have the same function REPLACENULL which used in SSIS, rather there are two ways you can replace the NULL values in ADF dataflow.
Approach 1: Combination of iif and isNULL function
Expression: iif(isNull(Name), 'Unknown', Name)
The function iif will check the condition isNull(Name), if Name have Null value it will return 'Unknown' otherwise original value will be returned.
Approach 2: By using iifNull function
The smartest solution is to use iifNull which will return exactly the same result we found via approach 1.
expression: iifNull(Name, 'Unknown') will return 'Unknown' if Name have NULL values otherwise it will return original value.
In summary, expression is similar to replace NULL values for both SSIS and ADF data flow, however, the function you need to use is different for two different tools.