Welcome to Julian Kuiters Sunday, May 27 2018 @ 01:37 PM AEST

SSIS: Handling Lookup Misses

  • Contributed by:
Integration Services 2005 / SSIS (DTS)

Let me direct you to an article by Ash on speed differences between two approaches to handing failed lookups in SQL Server Integration Services.

Failed lookups are likely to happen at some point, and in most cases you won't want your package to fail as a result, but rather a default or derived value or other handling routine should be used.

There are plenty of ways to handle redirected rows as a result of a failure. Ash provides a good reason for why ignoring the failures (instead of redirecting the row) is a better option: memcopy. In order for your row to go down a different path, the data must be copied into that path's buffer. Ignoring the failure means no copying of the row.

While the timings aren't a significate difference for a single lookup. If you were loading a huge amount of data into multiple fact tables, with lots of lookups, you could gain a lot of performance overall. Ash's test shows why benchmarking different methods can often find a more efficient method.