Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 04/21/2009 at 10:05 AM	SSiS\Doku

Das Redirect von Zeilen an den ErrorHandler funktioniert nicht in allen Lebenslagen.
Folgende Ereignisse lösen ein ReDirect aus:

• Probleme mit dem Statement selbst (z.B. fehlerhafte Parameterzuweisung, Syntax Fehler, etc)
  OLE Provider gibt dann DB_E_ERRORSINCOMMAND zurück
• Probleme im T-SQL Code, der mit Datenmanipulationen zutun hat
  OLE Provider gibt (z.B.) DB_E_INTEGRITYVIOLATION zurück

Man kapselte die Prozedur mit einem TRY _ Catch Block und erzeugt im Catch nach der Fehlerbehnadlung
wie z.B. dem Loggen in eine Fehlertabelle irgendeinen Fehler mit einem Insert/Update oder Delete erzeugen,
wie z.B.

• einem Insert mit einem NULL-Value für den Priimary Key oder
• einem Update mit dem Primary Key in der Update Klauses,
  
wie auch immer. Dadurch wird ein Fehler ausgelöst, dessen Fehler-Klasse die ist, die für ein Redirect gebraucht wird

Es gibt aber einen Workaround, mit dem man leben kann. Das Statement wird nach wie vor per TRY _ CATCH
gekapselt, wird aber keinen Fehler per RAISERROR() zurück, sondern setzt einen OUTPUT Parameter, der
per Parameter an eine zuvor in den Dataflow injizierte Spalte gebunden wurde.
Dies kann dann per Condition-Task ausgewertet werden und somit das Logging und nachgelagerte Aktionen
im Fehlerfall durchzuführen.
Ein ReDirect ist in diesem Fall nicht mehr notwendig und für Syntax-Fehler oder Zuweisungsfehler macht das
eh keinen Sinn

Hier ist der schematische Ablauf:

1. Erzeugung der zusätzlichen Spalte Ret als dtr_string(1024)
2. Statt dem Fehlerausgang den normalen dataflow ausgang wählen
3. ConditionTask prüft Spalte Ret auf einen Wert != '', was einen Fehler identifiziert, der im Catch Block
   dem OUTPUT Parameter zugwiesen wurde.
   Zusätzlich wurde ein Parameter @Rethrow eingebaut, mit dem das Verhalten (Raiserror / Verwendung
   OUTPUT Parameter) steuerbar ist. Siehe folgenden Code:

CREATE PROCEDURE test(
@Param1 INT
,@Param2 INT
    ,@Ret VARCHAR(1024) = '' OUTPUT
    ,@Rethrow BIT = 1
)
BEGIN CATCH
DECLARE 
@ErrorMessage    NVARCHAR(4000),
@ErrorNumber     INT,
       @ErrorSeverity   INT,
@ErrorState      INT,
@ErrorLine       INT,
@ErrorProcedure  NVARCHAR(200);

   SET    @ErrorSeverity = ERROR_SEVERITY()
SET    @ErrorNumber = ERROR_NUMBER()
SET    @ErrorState = ERROR_STATE();
SET    @ErrorLine = ERROR_LINE();
SET    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');


IF @Rethrow = 0 BEGIN -- Do not Raise the error again
SET @ret = 
N'Error ' + convert(varchar(10), @ErrorNumber) + 
', Level ' + convert(varchar(10), @ErrorSeverity) + 
', State ' + convert(varchar(10), @ErrorState) + 
', Procedure ' + convert(varchar(10), @ErrorProcedure) + 
', Line ' + convert(varchar(10), @ErrorLine) + 
', ' + 'Message: '+ ERROR_MESSAGE();
END ELSE BEGIN -- Raise the error again here
SELECT @ErrorMessage = 
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();
RAISERROR (
@ErrorMessage, 
@ErrorSeverity, 
2,               
@ErrorNumber,    -- parameter: original error number.
@ErrorSeverity,  -- parameter: original error severity.
@ErrorState,     -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine       -- parameter: original error line number.
)
END
END CATCH

Build in a new error handling. The StoredProcedure uses now Try-Catch blocks. In case of an error we return the
ErrorMessage in the OUTPUT parameter @ret. In the dataflow, after the execution of the OLE DB Command, we check
the content of the column Ret, which was inserted by a derivedColumn Task. If it is empty, we had succussfully done our
work, if not, there was an error and we count that and write it into the log, but continue with execution.
You could overwrite this behavior if you set the variable Rethrow to true, if so we raise the error again in the catch block
of the stored procedure, which causes the OLE DB Command to run into an error state instead of redirect rows.
This happens in each case, where we catch an error and rethrow it. Maybe the behavior depends on the error ID, which
is 50000 for an error raised by RAISEERROR with a user defined errorsting.
Anyway, we have to do that this way, if we want to log a detailed error and continue with execution. Each other try ends
in a Task error, which stops execution!

Let's first discuss a bit more on the difference between command-related errors and column / data related errors.  Some errors can occur if the command used has invalid syntax or if it uses a bad object name.  The input data processed by the command transform does not contribute to those errors.  In those cases, more often then not, users want to correct their command instead of looking for bad data and so, the command transform just fails execution instead of redirecting rows (if it redirected, it will end up redirecting all the rows since the command will always fail regardless of the data).  For those errors, OLE DB providers can return generic errors such as DB_E_ERRORSINCOMMAND to the Command transform.  Column / data errors can occur when the data in the columns cause the error, such as an invalid data conversion when inserting a badly formatted string into a date column or a value that violates an integrity constraint.  For those errors, users do want the rows redirected.  OLE DB providers usually return more specific errors (e.g. DB_E_INTEGRITYVIOLATION) along with which column contains the data that caused the problem.

So, one example is if a stored proc was written to catch all errors, including integrity violations that might have cause the OLE DB provider to return a DB_E_INTEGRITYVIOLATION to the Command transform, and in its exception handling, raises a generic error that the provider ends up returning as DB_E_ERRORSINCOMMAND to the transform.  The row redirection would not be triggered.  Depending on the database and its OLE DB provider, it might not be possible to produce an error that will cause the OLE DB command to redirect.  For example, if the OLE DB provider is written to always return DB_E_ERRORSINCOMMAND regardless of what the actual error was reported by the database.

 Since we've shipped the command transform, we have seen cases where command-related errors are being returned for data related errors, causing row redirection to not occur.  So, for the next version of Integration Services, we've made the Command transform more accommodating by having it look at DB_E_ERRORSINCOMMAND and try to redirect if possible.

 Hope this clears things up a little bit more.