In my package, I'm reading from a flat file. The file can have a date specified in one of the fields, or it can be left as a string of 0's.
If it is 0, I want to insert it as a null into my database. However I am unsure how to work around Date not being nullable.
Currently I am checking the to see if the file has 0's for this date in my script component. If it is 0, I set it to a default date of 1/1/1900.
Then in a derived column, I am trying to replace my out_period_end_dt column if the year of my Date is 1900 to set it as a null value, or just leave it as is otherwise. My expression:
YEAR(out_period_end_dt) == 1900? out_period_end_dt = null : out_period_end_dt
It throws an error stating that my expression is invalid. My column out_period_end_dt is of the DT_DBDATE type. I tried looking for a nullable date data type, but I wasn't able to find one. Suggestions?
YEAR(out_period_end_dt) == 1900? NULL(DT_DBDATE) : out_period_end_dt