When I read NULL my first thought is Blank/Empty. Since people have different ways of
implying a column may be empty, it is very common to need to cleanse out any
“NULL”values.
For this Blog I am going to not only show how to handle a NULL value, I am also
going to show you how to handle values that would imply NULL in a data source,
such as a blank space in Excel, or an Oracle Essbase source where you would see
a value like “#MISSING” or “#MI”.
The most common way to handle these issues would be using a Derived Column
Transform in the Data Flow.
Here is an example handling each of the scenarios I just mentioned-
(REPLACE(UPPER(<YourColumnName>),"#MI","0"))
I use UPPER because this is case sensitive so to guarantee
we capture all of these values we need them all to be capitalized. Substitute “#MI” for any
default value in your Data Source.
ISNULL(<YourColumnName>) ? "New Value" : <YourColumnName>
This will verify a NULL. From there you can change it to whatever
you want. In this
scenario you would still use a value that implies it needs to be updated, but
the Destination Table may not allow NULLs.
ISNULL(<YourColumnName>) || TRIM(<YourColumnName>)=="" ? "New Value" :
<YourColumnName>
This will work if you have a nice mixture of NULLs || (OR)
a Blank space in your Data Source. You can add a value inbetween the
quotation marks to substitute another value used.
TRIM(<YourColumnName>) == "" ? (DT_STR,4,1252)NULL(DT_STR,4,1252) :
<YourColumnName>
This will turn a Blank space into a NULL value.
Example:

I’d also like to point out that the BIxPress Expression editor has these
Expressions available in the Expression Editor, so you can always build your
Expressions in there and Evaluate them as well to ensure you are getting the
expected values returned.
Thanks for reading.