By: Koen Verbeeck | Comments (16) | Related: More > Integration Services Error Handling
Problem
When there is an error in the SQL Server Integration Services (SSIS) data flow, you have the ability to redirect the error onto an error path. The system provides you with the error code and the ID of the column causing the error. However, for our logging requirements we need to have the column name as well. How can we achieve this in SSIS?
Solution
In versions of SSIS before SQL Server 2016, there was no easy way in the data flow to retrieve the name of the column that caused a component to fail. It's easy to retrieve the error description, but for the name of the specific column you only had two options:
- Rely on 3rd party components, which are not always an option for every company
- Use an elaborate script component scanning a copy of the package
The problem is you only get the column lineage ID and there is no easy way to retrieve which column it actually belongs. Luckily this has changed in SSIS 2016 and this tip will explain how you can fetch the column name without too much effort. The solution provided in this tip is valid for SSIS 2016 and later versions.
Test set-up
I used a very easy package, with only one data flow:
The data flow has the following structure:
The source uses this query to retrieve some sample data:
SELECT Nominator = 10, Denominator = 2 UNION ALL SELECT Nominator = 20, Denominator = 4 UNION ALL SELECT Nominator = 30, Denominator = 6 UNION ALL SELECT Nominator = 40, Denominator = 0 UNION ALL SELECT Nominator = 50, Denominator = 10;
The Derived Column transformation calculates a multiplication and a division. The division will error out on row 4 because of a division by zero error.
The Derived Column is configured to redirect errors to the error path:
In previous versions of SSIS, you could see the error ID and the column ID when you ran the package:
Note that the data viewer also shows the error description, but this seems to be extra functionality of the data viewer since this column is not part of the error output.
During debugging, you can find the error column yourself by using the lineage ID of the column - 7 in this example - but for automated logging at the server side it would be useful if this lookup process would be simplified and automated.
Retrieving the error column in SSIS 2016 or later
In the 2016 release of SSIS, a new function has been added to the SSIS library: GetIdentificationStringByLineageID. This function allows us to retrieve the column name by using the lineage ID. To test this, we need to add a script component as a transformation to the data flow.
In the script component editor, select the ErrorCode and ErrorColumn columns as inputs.
Create two new output columns, one for the error description and one for the column name. Make sure the width of the description column is wide enough or the script might return errors. I have chosen 5000 characters as the width in this example.
The .NET code is pretty simple: you only need three lines of code to retrieve the description and the column name. These lines should be added in the ProcessInputRow method of your input buffer.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
}
When we now run the data flow, we can see the results of the script component:
Note: when you add a data viewer to the error path in SSIS 2016 (or later), the data viewer will display column name of the error column, just as the error description was shown in earlier versions of SSIS. However, the column is not added to the output and is thus not logged.
Conclusion
SSIS 2016 introduces a new function in its API allowing you to easily retrieve the column name of the column that caused an error in the data flow. This improves the logging capabilities of SSIS and it reduces the complexity of existing solutions.
Next Steps
- Try it out yourself! Download SQL Server Data Tools and experiment with this new feature.
- The official documentation on What's New in SSIS 2016.
- If you want to know more about error handling in the data flow, read Error Handling in Data on the MSDN website.
- More tips about error handling and logging:
- For more SQL Server 2016, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
