Environment: SQL Server 2008 R2 with SSIS and SSAS.
Short question: Is there a way to get SSIS to send a string of data from a failed search to a trace event?
Long task: I collect several SSIS packages that perform a series of conversions based on data from tables that have up to two million rows. The final destination is the SSAS cube. All searches should be successful - a failure indicates that a data quality problem has leaked, so the error in all ETLs is correct if the search fails.
However, there seems to be no easy way to get the SSIS Lookup component to report which string failed when it logs "Row yieled no match during search" in the trace log. I am looking to see if I can do anything to actually catch the failed string and get the string data written to the trace at the same time.
At the moment, I have to resort to submitting inappropriate lines to the CSV file for analysis, but this means that takeon continues processing, which I don't want. In addition, linking a file to each search component means managing a large number of additional files (which also require connection manager settings related to them). I could theoretically manage one file if I fed all the output to the Union transform, but when I deal with packages that have up to 10-15 Lookup transformations, it gets very dirty.
I am wondering if there is a way to connect to the OnError event to get this data, but if there is, then this is not obvious.
Any other ideas are welcome. I cannot believe that I am the only one who wonders how to do this, but my stackoverflow-fu and google-fu have left me, and I cannot (strangely enough) find anything on this ...
Hooray!
source share