When you are working with ETL/ELT, sometimes you may need to transform NULL into a meaningful value. If you worked with SSIS, you probably know how you handle that. This blog post will describe how do we typically do this in SSIS and then how the very same task can be performed in an ADF Dataflow.

Consider that we have a .csv file where the Name columns have a NULL value for 2nd record (Fig 1). I highlighted the absence of any value in the image.

Fig 1: Sample .csv file with NULL record

After connecting the .csv file through the flat file source in an SSIS data flow, we can debug and view the record through the data viewer, which will look like below Fig 2

Fig 2: Result in SSIS data flow - data viewer

If you would like to replace the NULL value with a more meaningful value, you need to use Derived Column activity and an expression. SSIS has a REPLACENULL function, which will replace NULL with the expected value that you want. The syntax is:

The expression: REPLACENULL(Name,"Unknown")

The above expression will return 'Unknown' when Name is NULL, otherwise it will return the original value.

Fig 3: Expression in SSIS Data flow to replace NULL with 'Unknown'

When it comes to ADF data flows, the regular expression for detecting NULL is similar to the SSIS expression. The isNull() function only give you true or false, and the isNull() function can take only one argument. Below in Fig 4 the function takes the argument, Name, and returns True if the value is NULL.

Fig 4: ADF dataflow isNull function

Now, let's find out how to transform NULL value into something meaningful in an ADF data flow. ADF doesn’t have the same function, REPLACENULL(), which is used in SSIS. Instead there are two ways you can replace the NULL values in ADF dataflow.

Approach 1: Combination of iif and isNULL function

We can combine two functions in an expression. In the expression below, iif() will check the condition, isNull(Name). If Name has a Null value, the function will return 'Unknown', otherwise original value will be returned.

Expression:

Expression: iif(isNull(Name), 'Unknown', Name)

You can see this implemented below in Fig 5.

Fig 5:  using iif and isNull in ADF dataflow

Approach 2: By using iifNull function

Another, and perhaps the smartest, solution is to use iifNull(), which will return exactly the same result we found via approach 1 but more cleanly and easier to use. The syntax is:

expression: iifNull(Name, 'Unknown')

This will return 'Unknown' if Name has a NULL value. Otherwise it will return the original value.

Fig 6:iifNULL function to replace NULL value

In summary, the expression is similar to replace NULL values for both SSIS and ADF data flow, however, the function you need to use is different for the two different tools.