We have a Lookup to match incoming records from temp table and filter out duplications before entering the destination table. The temp and destination tables have exactly the same definition. The matching compares 3 columns with type of text, numeric, and datetime.
It works correctly under full cache mode, but will run out of memory when data grows to realistically massive size. The Look up tries to load the entire destination table into memory, and triggers endless swapping.
For performance tried to change it to partial cache and no cache, and the changes breaks correctness because the equal values on datetime column fails to match and repeats are escaping the filter into destination data table.
The other columns are in text and numeric types, and they seem to be OK. Just for test, if exclude the datetime column the Lookup matches as expected with the rest columns.
The environment is SQL Server 2016, Visual Studio 2015, and Windows Server 2016. And the matching failure happens in Visual Studio debugging. More information available if needed, and thanks a lot in advance.
I have a tentative fix working now, just not sure if this is the best practice. I modified the SQL query to add a column of string in style 121 ("yyyy-MM-dd hh:mm:ss.fff") including the milliseconds, then use a string compare.
Thanks to @PrabhatG for the hint on how to find out which part of datetime is breaking, whether it is date or time of the day.
Got the answer from the below link: https://social.msdn.microsoft.com/Forums/en-US/a35e21cf-735c-4061-929c-b117389e38b5/ssis-lookup-not-matching-on-equal-values-of-datetime-if-cache-mode-is-partial-or-no-cache?forum=sqlintegrationservices
If you have a better solution, please kindly share with us.