May 31, 2013 at 12:07 pm
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)
June 6, 2013 at 3:22 am
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
June 6, 2013 at 5:55 am
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