Nested IF ELSE in a derived column

Question

I have the following logic to store the date in BI_StartDate as below:

If UpdatedDate is not null then BI_StartDate=UpddatedDate
ELSE BI_StartDate takes EntryDate value , if the EntryDate is null then BI_StartDate=CreatedDate
If the CreatedDate IS NULL then BI_StartDate=GetDATE()

I am using a derived column as seen below:

ISNULL(UpdatedDateODS)  ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :
(ISNULL(CreatedDateODS) ? GETDATE()  ))

I am getting this error:

The expression "ISNULL(UpdatedDateODS) ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :(ISNULL(CreatedDateODS) ? GETDATE() ))" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" is not valid.

KeithL KeithL · Accepted Answer · 2020-02-19T19:52:27

You are looking the first non-null which is a coalesce which doesn't exist in SSIS Data Flow (derived Column).

I'd suggest a very simple script component:

Row.BIStartDate = Row.UpdateDate ?? Row.EntryDate ?? Row.CreatedDate ?? DateTime.Now;

This is the Input Columns Screen:

This is the Inputs and Outputs:

And then you add the above code to Row Processing section:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
         * Add your code here
         */

        Row.BIStartDate = Row.UpdateDate ?? Row.EntryDate ?? Row.CreatedDate ?? DateTime.Now;
}

Nested IF ELSE in a derived column

2 Answers