“No Match Output” in SSIS 2008

The hairy Irishman (his description – I just call him Sacha) is presenting to the Adelaide SQL Server User Group today, talking about improvements in SQL Server 2008 Integration Services. Right now he’s talking about the No Match output of the Lookup Component, and I’m wondering why I had always just accepted that in SQL 2005, rows that didn’t match a lookup would get pumped out to the error output.

For those of you who are saying “Sorry, what?”, let me explain…

If you have a data flow in Integration Services which is missing a particular piece of information, and you need to get that information from another source, you are likely to do a Lookup transformation, which can perform a query to find the values to be inserted into each row in the flow. Of course if the data is just in two tables, you’d just do a join in your original source query, but as soon as you’re talking about files, you don’t have that luxury in quite the same way. Sure you could populate a table and then read it back, but a Lookup may end up being much quicker.

Now, in SQL Server 2005, if a row couldn’t find the looked up value, it would be considered an error, along with truncation errors and errors in connection to the lookup source. I just accepted this as okay before, but the more I come across the No Match output (which can be used as well as the error output), the more I like it. So I can much more easily separate the rows that have caused errors from the rows that just couldn’t be found in the lookup source.