CODEWITHSUNDEEP
sql-serverssisexpressionetlderived-column
Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Nested IF ELSE in a derived column

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

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.

Upvotes: 3

Views: 1024

Answers (2)

Hadi
Hadi

Reputation: 37313

From syntax perspective, the nested if-else condition is not written well, since you have to make sure that all possible output should have the same data type, also you didn't mention the last "else" condition:

ISNULL(UpdatedDateODS)  ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :
(ISNULL(CreatedDateODS) ? GETDATE() : **<missing>** ))

From logical perspective, you the expression may throw exception since you are using EntryDateODS column if ISNULL(UpdatedDateODS) is true, while you should check if EntryDateODS is not null before using it, I suggest that the expression is as following:

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

As mentioned above, if UpdatedDateODS , EntryDateODS, CreatedDateODS and GETDATE() don't have the same data type then you should cast to a unified data type as example:

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

Upvotes: 2

KeithL
KeithL

Reputation: 5594

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:

enter image description here

This is the Inputs and Outputs:

enter image description here

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;
}

Upvotes: 3

Related Questions