title: SSIS Lookup Component -- identify failed rows / send to trace output?
body: <p><strong>Environment:</strong> SQL Server 2008 R2 with SSIS and SSAS.</p>
  
  <p><strong>Short question:</strong> Is there anyway to get SSIS to send the data row from a failed lookup to a trace event?</p>
  
  <p><strong>Long question:</strong> I'm pulling together a number of SSIS packages that do a number of transformations on data from tables that have up to a couple of million rows in them. The ultimate destination is an SSAS cube. All lookups should suceed -- failure indicates that a data quality issue has seeped in, so failing the entire ETL on a failed lookup is correct.</p>
  
  <p>However there doesn't seem to be any easy way to get the SSIS Lookup component to report on which row failed when it logs "Row yieled no match during lookup" to the trace log. I'm looking to see if there's anything I can do to actually catch the failed row and get the row data logged in the trace at the same time. </p>
  
  <p>At the moment, I'm having to resort to feeding non-matching rows to a CSV file for analysis, but this means the takeon then continues processing, which I don't want. In addition, hooking a file onto every lookup component means managing a large number of extra files (Which also need their associated connection managers configuring). I could, in theory, manage a single file if I fed all outputs into a Union transform, but when I'm dealing with packages that have up to 10-15 Lookup transforms, this gets messy very quickly.</p>
  
  <p>I'm wondering if there's any way I can hook into the OnError event to get this data, but if there is, it's non-obvious. </p>
  
  <p>Any other ideas welcome. I can't believe I'm the only one that's wondering how to do this, but my stackoverflow-fu and google-fu has abandoned me and I can't (oddly enough) find anything on this...</p>
  
  <p>Cheers!</p>