SSIS: Handling Lookup Misses

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.

  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Blogter
  • BlogMemes
  • Yahoo Buzz

SSIS: Handling Lookup Misses | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.

Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 Copyright © 2018 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.