Thursday 3 September 2015

SQL Server 2016 CTP 2.3 Get error columnname

Case
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

6 comments:

  1. Get the Error ColumnName does not work for me, I got an error with 'GetIdentificationStringByLineageID', it does not exists for ComponentMetadata

    Do it have to add more code for that 'GetIdentificationStringByLineageID'? or add any reference?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. They changed it in RTM:
      IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
      Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

      Delete
  2. I have SSDT 2015. I am trying to catch
    Row.ErrorColumnDescription = component130.GetIdentificationStringByID(Row.ErrorColumn);

    It does nor work.It throwing and exception HRESULT: 0xC0047072

    ReplyDelete
  3. HRESULT: 0xC0047072

    It is not working

    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

    ReplyDelete
    Replies
    1. Try the update link on top. They changed the code between CTP and RTM

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...