CODEWITHSUNDEEP
ssis
Daniel Sh.
Daniel Sh.

Reputation: 2074

Why am I getting an error in derived transformation while validating datetime field?

I have a source with 0000-00-00 in a datetime field. When I try to copy that to an SQL destination the error pops up.

Im trying a derived column to fix it. When it's 0000-00-00 change it to NULL. Here's the pic.

enter image description here

Why isn't it working? I've tried changing the 0000-00-00 00:00:00 to 0000-00-00 and still, it won't work.

Upvotes: 1

Views: 865

Answers (2)

user756519
user756519

Reputation:

Explanation:

Try the following expression to resolve the issue. Since your incoming field is of DATETIME data type and you want to ignore all the values that have zeroes in them. You can find the DAY of the given datetime value and check whether if it is zero or not. If it is zero, you need to use the NULL function NULL(DT_DBTIMESTAMP) else you can simply use the field name, which contains the valid date and time value. Then, type cast the whole expression into data type DT_DBTIMESTAMP

Expression:

(DT_DBTIMESTAMP)(DAY([FechaHoraCorteAgente]) == 0 ? NULL(DT_DBTIMESTAMP) : [FechaHoraCorteAgente])

Upvotes: 2

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

You should use NULL(DT_DBTIMESTAMP) instead of NULL in your expression.

Upvotes: 1

Related Questions