Updating values in a Database target to (null)

We have a mapping setup between two database tables that should take care of the following:

-          Source table needs to update existing records in the target table.

-          Source table needs to insert new records into the target table.

-          Existing records in the target table that are not featured in the source table need to be left alone.

For this we have a one-to-one data mapping between the two tables.

On the target activity we have the Update/Insert option selected.

The setup works fine except in the following scenario:

-          A record already exists in the Target table and has value X in field 1 in the Target table.

-          The same record is in the Source table, but has (null) in field 1.

-          Expected behavior is that field 1 in the Target table is getting updated to (null). This doesn’t happen however, the old value remains.

Illustrated Example:

Before mapping has run:

Source table:

Target Table:

After Mapping has run:

Expected:

Source table:

Target Table:

Observed:

Source table:

Target Table:

Is there any way we can change this behavior to what we’re expecting from it?

Solution:

To update the column "Field_1" of the target table with null value you have to check the "Update Empty Tag" inside Advanced Properties of Database Target Schema.