locked
Logging lookup failures RRS feed

  • Question

  • Hi all,

     

    I have a package which moves data from a SQL database to a SQL data warehouse.

    The package includes tons of lookups, all of them set to fail when a match is not found. The idea is that having a relational source with referential integrity in place, lookups should always succeed unless there is a problem with the package itself. I don't want to go to each lookup transformation in my code and explicitly log failures, I would rather rely on standard SSIS error logging mechanism to log the error into a text file for instance. Such log will tell me which transformation failed, but it would be nice to have some context, like the full data row that caused the problem, and maybe even the lookup cache. Is this possible?

     

    Thanks

    Wednesday, March 2, 2011 4:53 PM

Answers

  • You then need an additional step. But as an aside, I want to tell you if you are going to stop right away then why do you want to collect the offending rows?

    So the following approach can be a better choice:

    After the record processing is done (whether with errors or not) examine the error output file size or date (can provide with help on this). If it exists/new/full stop the package using a Script Task (also can help here).

    Please let us know if this is the ideal solution for you.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Tony Chain Friday, March 25, 2011 2:08 AM
    Thursday, March 3, 2011 6:34 PM

All replies

  • Redirect the error rows to a file.

     


    Arthur My Blog
    By: TwitterButtons.com
    • Proposed as answer by Todd C Wednesday, March 2, 2011 11:34 PM
    Wednesday, March 2, 2011 7:04 PM
  • Thanks

     

    If I redirected the error rows to a file the package keeps running, I want it to stop and throw an error right away

    Thursday, March 3, 2011 5:59 PM
  • You then need an additional step. But as an aside, I want to tell you if you are going to stop right away then why do you want to collect the offending rows?

    So the following approach can be a better choice:

    After the record processing is done (whether with errors or not) examine the error output file size or date (can provide with help on this). If it exists/new/full stop the package using a Script Task (also can help here).

    Please let us know if this is the ideal solution for you.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Tony Chain Friday, March 25, 2011 2:08 AM
    Thursday, March 3, 2011 6:34 PM
  • Thanks Arthur,

     

    The ETL Job will be deployed on the customer site and I'm trying to make my life easy in case of failure, i.e. I want to know the offending row (a guid) and the exact lookup that failed to help me debug the problem.  If I just set the lookup so that it fails the package, I can't get the offending row. Redirecting the rows to error output seems like a good idea, it lets me get the row info and an error number (which is failed lookup) but not the exact lookup operator that failed. Again, I have tons of lookups and I would have to modify each of them and pass the name of the lookup operator to mthan going to y error log table. I was hoping there was some sort of easier way than just doing this for each lookup, but I guess I can do it. Checking the number of error rowsat the end of the task and using a script to stop the package seems like a great idea, thanks

    Thursday, March 3, 2011 8:34 PM