Thursday, September 5, 2013

Limitation with YEAR() function in SSIS

I learned something new today while researching an ETL failure:
The YEAR() function in SSIS has a lower limit of 1-1-1753.

Even though the DT_DATE and DT_DBDATE support dates earlier than 1-1-1753, the YEAR() function does not and will fail.

For validation in a derived column transformation, I was using this logic:
YEAR(data_field) < 1900? (DT_DBDATE)"1-1-1900":data_field

And when the date 8-23-0203 came thru, the package failed. The error was non-descript:

An error occurred while evaluating the function.

So, be aware...