I have an SSIS data flow conditional split transformation that is returning a >= result when it should be returning a > result.
From the beginning... My SSIS package executes a SQL task that stores a max(date) from one table in variable User::max_date of type DateTime that has package scope.
My package then proceeds to execute a Data Flow task which has a conditional split transformation in it that has the condition date > @[User::max_date]. The result set is always a single row which has the DateTime value equal to User::max_date, that is it returns the row that contains max(date) if no more rows have been added with more recent dates than max(date). Which is the unexpected behaviour.
Furthermore, the other output from the conditional split is incorrect -- it's all the rows with date < @[User::max_date] and not all the rows with date <= @[User::max_date] as one would expect.
Why is this happening?
Update:
I've figured out how this is happening, but not why. User::max_date is storing a DateTime and my date value from my Data Flow task is a DT_DBTIMESTAMP. Upon comparing them with a DATEDIFF(..) they differ only at milliseconds and beyond.
So I can work around the problem, but I have no idea why this would be implemented this way.
Comments?