CODEWITHSUNDEEP
sql-servercachingssislookup
James
James

Reputation: 2143

SSIS Lookup not matching on equal values of datetime if cache mode is partial or no cache

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.

Upvotes: 1

Views: 1589

Answers (2)

zfrank
zfrank

Reputation: 456

This is long past answered with a working solution, but in case anyone else ends up here and wants a solution that doesn't involve changing the database schema:

The problem appears to be with casting from SSIS's DT_DBTIMESTAMP type to SQL's DATETIME type, so we just have to make sure that the values it's comparing will be equal when they're supposed to be.

Open up the Lookup Transformation Editor, go to the Advanced tab, then check "Modify the SQL statement". Find the part of the WHERE clause that's doing the date comparison, then change its "?" to "convert(datetime,?)"

Mine went from this:

where [refTable].[Import_Date] = ?

To this:

where [refTable].[Import_Date] = convert(datetime,?)

And it all worked just fine after that!

Upvotes: 0

James
James

Reputation: 2143

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.

Upvotes: 0

Related Questions