In SSIS, there is no direct method to get the error description when error occurred during data Load and transformation.
We can only redirect the failed records along with Error Code to log file (or any destination component). Since the Error Code is not very useful, we may need to store the Error Description along with the failed row. Even though there is no direct mechanism there is a workaround to achieve. The workaround is to add Script Component.
Let’s explore with an example.
Below is the sample package, designed to load Sales Order details from flat file to Sales database. Note the Script Component “SC-GetErrorDesc” placed in between Sales Database and Log Error tasks.
The next screen shows the configuration of Script Component to get the Error Description.
1. Choose the ErrorCode from the available Input columns.
2. Add new output column (ErrDesc) in the Inputs and Outputs section
3. Edit the script
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
Row.ErrDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
}
4. Finally the result from Script component is stored in the flat file. When you map the column you will notice a new columns named ErrDesc in the Available Input Column side, this new column is created and populated within the script component task.
