CODEWITHSUNDEEP
ssis
user1902849
user1902849

Reputation: 1141

Derived column expression

I've value that need push to database. The source is EventDate. It should only contain data value in the format DD-MONTH-YY and database data type is datetime.

Recently there an invalid value in the EventDate where it contain character value 'Unknown' and this caused insertion to database failed.

After google, found out can use Derived column method to transform 'Unknown' to null value. But expression that I wrote was failed. Please help

(DT_DATE)(TRIM(EventDate) == "Unknown" ? (DT_DATE)NULL(DT_DATE) : EventDate)

Upvotes: 1

Views: 62

Answers (1)

Inus C
Inus C

Reputation: 1551

You do not need to cast the NULL part as something again. setting the NULL([datatype]) should be fine.

You might also need to cast the column as unicode first to be able to TRIM. The size might be bigger than 20, just check.

TRIM((DT_WSTR,20)EventDate) == "Unknown" ? NULL(DT_WSTR,20) : EventDate

But this should be more or less it. If there are still some issues, add and remove some cast types for the fisrt and last part to see if it changes. Might also still be NULL(DT_DATE) as you had it.

Upvotes: 2

Related Questions