Before SSIS 2016 there was no way to get the name of the column that caused the error. Atleast not without a custom transformation or a very complicated script that looped through a copy of your package to get all details. Now there is a simple solution available.
Solution
There was already a script available to get the error description. Now you can use a similar way to get the columnname. First download SSDT 2015 (SSDT and SSDT-BI have been merged!).
* UPDATE: code below outdated / changed click here for new code *
1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.
Redirect errors to Error Output |
2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".
Add Script Component Transformation |
3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.
Input Columns |
4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.
Output Columns |
5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following two lines of code.
// C# Code public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode); Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn); }
And VB.NET code
' VB Code Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode) Row.ErrorColumnName = Me.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn) End Sub
6) Testing
Now add a Data Viewer behind the Script Component Transformation to see the results
Error Description and ColumnName |
Get the Error ColumnName does not work for me, I got an error with 'GetIdentificationStringByLineageID', it does not exists for ComponentMetadata
ReplyDeleteDo it have to add more code for that 'GetIdentificationStringByLineageID'? or add any reference?
This comment has been removed by the author.
DeleteThey changed it in RTM:
DeleteIDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
I have SSDT 2015. I am trying to catch
ReplyDeleteRow.ErrorColumnDescription = component130.GetIdentificationStringByID(Row.ErrorColumn);
It does nor work.It throwing and exception HRESULT: 0xC0047072
HRESULT: 0xC0047072
ReplyDeleteIt is not working
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
Try the update link on top. They changed the code between CTP and RTM
Delete