Error Handling

  • I am fairly sure I already know the answer to this, but any input others may have, or experience would be handy, in case I have overlooked some idea.

    Suppose you have a large warehouse system, a combination of SSIS and stored procs, and this system was not built with any form of error handling. This is the start point.

    Management wants there to be some "switch to throw" which would cause the entire process to ignore and continue to process on any errors, such as a duplicate key insert.

    (We have been over and over all the serious problems with such an idea)

    Is there actually such a thing? Or some method which one could copy/paste to every SSIS or stored proc which would suddenly handle the various errors that could possibly happen?

    As a note this idea got started because there was an issue with the cube, and the cube was set (originally for a single run) to ignore all errors. So it had a "switch", from the management perspective.

    To the best of my knowledge, to handle the errors, we need to change the code, all of the places where it could be a problem to properly handle the errors. That there is no sort of configuration switch at the database or SSIS server level which can be implemented to drop all error records, but continue on and process all non error records.

    Any thoughts? (Besides the disaster which looms in the future when ignoring errors entirely)

  • I could only think you need to add error handling in your code

    Example: you can think of following code to handle the duplicate key issue

    BEGIN TRY

    CREATE UNIQUE CLUSTERED INDEX ix_tblname ON tblname (col1,col2)

    END TRY

    BEGIN CATCH

    ;WITH dupes AS

    (SELECT * , ROW_NUMBER() OVER ( PARTITION BY col1,col2 ORDER BY col1, col2) AS Rownum FROM tblname )

    DELETE FROM dupes

    OUTPUT DELETED.* INTO #Log

    WHERE Rownum > 1

    CREATE UNIQUE CLUSTERED INDEX ix_tblname ON tblname (col1,col2)

    END CATCH

    [font="Calibri"]Raj[/font]
  • That is what I was thinking as well. Essentially the management wants the existing code based fixed to handle errors without spending any time coding. None of us believe that is possible. I thought to myself, if there was some clever or unique way to do it, someone on this board would know.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply