ssis derived column - check if date field is null

Question

In SSIS derived column I need to check if a Date field is empty or null, I'm using this expression but I got an error:

TRIM([date field]) == "" ? NULL(DT_DATE) : TRIM([date field])

The field is of DT_DATE data type. What's wrong in that?

I've used expressions like this successfully:

TRIM(String_field) == "" ? NULL(DT_WSTR,255) : TRIM(String_field)

What is the error you get? – digital.aaron Feb 07 '20 at 17:26 — digital.aaron, Feb 07 '20 at 17:26

score 3 · Accepted Answer · answered Feb 07 '20 at 17:49

3

You are combining data types (if true is a date and false is a string).

This is the correct formula:

TRIM([date field]) == "" ? NULL(DT_DATE) : (DT_DATE)TRIM([date field])

I am assuming [date field] is a string.

answered Feb 07 '20 at 17:49

KeithL

5,348
3
19
25

and what if [date field] is dt_date? – marko Feb 10 '20 at 11:12
@marko be aware that this expression does not handles nulls. – Hadi Feb 10 '20 at 12:44
1

@marko --> if [date field] was a date then both of your tests fail. the TRIM() as well as = "" – KeithL Feb 10 '20 at 19:21
This solution will throw an exception if the field contains NULL – Yahfoufi Feb 11 '20 at 10:40

score 1 · Answer 2 · answered Feb 07 '20 at 21:20

Concerning the error, it is related to the derived column result data type, since in conditional operators (?:) both true and false expression must generate the same data type.

In addition, your expression doesn't check for NULL values, you should use the following expression:

TRIM(REPLACENULL([date field],"")) == "" ? NULL(DT_DATE) : (DT_DATE)TRIM([date field])

Also, you have to make sure that [date field] can be parsed as date, so (DT_DATE)TRIM([date field]) will not throw an exception, you can use the error output to handles the value that doesn't contain a valid date.

ssis derived column - check if date field is null

2 Answers2