COALESCE: It returns the first not null column value in the list of columns supplied to the COALESCE function
Example :
select
COALESCE (col1,col2,col3) as ouput_column
from Table1
Possible Output values:
- if col1 is not null for a record then the value in ouput_column is col1
- if col1 is null and col2 is not null then the value in ouput_column is col2
- if col1 is null and col2 is null and col3 is not null then the value in ouput_column is col3
Implementation in SSIS:
The COALESCE function can be implemented in SSIS inside the Derived Column Task using "?:(Conditional)" present in "Operators" folder and "ISNULL()" present in "Null Functions" folder in top right corner of the Derived Column Task window as shown below.
