A few months back I reported a minor but annoying bug in the way NULL dates are handled in SSIS. It’s documented here, but basically, if you use an Execute SQL Task to set a DateTime variable to NULL, the value is 11/30/1999. However, a NULL date in SSIS returns 12/30/1899.
To explain a bit more, the SSIS package variables can’t hold NULL in the same way that a database column can. The variable always has to have a value in it. You can set a variable to NULL, but this just sets a default value in the variable. To see an example of this, create a new variable of type DateTime, set the EvaluateAsExpression property to TRUE, and put this expression in it:
NULL(DT_DATE)
The resulting value will be 12/30/1899. It isn’t uncommon to translate NULL values to a known, but unlikely to be used, value. However, since the Execute SQL Task is setting the NULL value to 11/30/1999, and the NULL value in SSIS is 12/30/1899, you can’t easily compare the value of a DateTime variable to NULL(DT_DATE) to see if the variable is “NULL”.
Fortunately, with this fix, this should be a little easier. Unfortunately, I don’t know exactly when we will see it released.