Error Handling in SSIS
The most common methods to handle the errors in the SSIS package are
1.Redirecting the Error Row along with the Error Code and Error Column in the DFT
2.Make use of event handlers for logging the errors in the Control Flow
By making use of Event Handlers you can Log custom messages in to custom tables during OnWarning , OnError events on the control flow tasks.This could be done by inserting custom error messages into the table using execute sql task or a Script component.
However while ETL i.e in a DFT you could redirect just the error rows and log error message for them.By Redirecting the Error Row you get the data of all the fields in the Error Row along with couple of additional fields ErrorCode and ErrorColumn.The Error Row itself could be used while logging.Its a shame that SSIS does not provide you the information of the exact column where the Error Occurred.The Error Column should not be mistaken for the column name , if you noticed it before its data type is an integer and it provides the lineageid of the respective column where the Error Occurred.
LineageId is something which is generated by the SSIS itself to identify the fields individually.If you open SSIS package as an XML you could view these lineage Ids assigned for each of the fields .
Now I am looking at the other option of getting the Error Description from the Error Code and using that in the custom Log Table which I created.Again the Error Description can be derived from Error Code using the following C# Script in the Script Component
Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
The Error Description here again is not meaningful since it is a system generated error message and is of little use to trace down the Error Column.
To get the Error Column there are few Custom Components available online , but due to lack of supporting documents and security reasons I tried not to implement them.
The most common methods to handle the errors in the SSIS package are
1.Redirecting the Error Row along with the Error Code and Error Column in the DFT
2.Make use of event handlers for logging the errors in the Control Flow
By making use of Event Handlers you can Log custom messages in to custom tables during OnWarning , OnError events on the control flow tasks.This could be done by inserting custom error messages into the table using execute sql task or a Script component.
However while ETL i.e in a DFT you could redirect just the error rows and log error message for them.By Redirecting the Error Row you get the data of all the fields in the Error Row along with couple of additional fields ErrorCode and ErrorColumn.The Error Row itself could be used while logging.Its a shame that SSIS does not provide you the information of the exact column where the Error Occurred.The Error Column should not be mistaken for the column name , if you noticed it before its data type is an integer and it provides the lineageid of the respective column where the Error Occurred.
LineageId is something which is generated by the SSIS itself to identify the fields individually.If you open SSIS package as an XML you could view these lineage Ids assigned for each of the fields .
Now I am looking at the other option of getting the Error Description from the Error Code and using that in the custom Log Table which I created.Again the Error Description can be derived from Error Code using the following C# Script in the Script Component
Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
The Error Description here again is not meaningful since it is a system generated error message and is of little use to trace down the Error Column.
To get the Error Column there are few Custom Components available online , but due to lack of supporting documents and security reasons I tried not to implement them.
