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:

UniqueID

Field_1

Field_2

Field_3

1

A

B

C

2

K

L

M

3

(null)

Y

Z

 

 

Target Table:

UniqueID

Field_1

Field_2

Field_3

1

AA

BB

CC

2

K

LL

M

3

X

YY

ZZ

4

AB

CD

EF

 

 

After Mapping has run:

Expected:

Source table:

UniqueID

Field_1

Field_2

Field_3

1

A

B

C

2

K

L

M

3

(null)

Y

Z

 

 

Target Table:

UniqueID

Field_1

Field_2

Field_3

1

A

B

C

2

K

L

M

3

(null)

Y

Z

4

AB

CD

EF

 

 

Observed:

Source table:

UniqueID

Field_1

Field_2

Field_3

1

A

B

C

2

K

L

M

3

(null)

Y

Z

 

 

Target Table:

UniqueID

Field_1

Field_2

Field_3

1

A

B

C

2

K

L

M

3

X

YY

ZZ

4

AB

CD

EF

 

 

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.

Have more questions? Submit a request

0 Comments

Article is closed for comments.