Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: Get error descriptions

If you use an error output then you will find that you get given an error number and an error column (i.e. the column in which the error occurred) but you do not get an error description. Well not to worry because youo can get the error description using a script component.

Here I've built a data-flow that contains an error in the source data.

20050808ErrorDataFlow.JPG

It is expecting integers in the source but in one row there is a string - hence the error. Here's the source file:

20050808SourceFile.JPG

 

So as I said, you can get the error using a script component. You can see this script component in the screenshot above - I've called it "Get Error Description". Its a synchronous component and the first thing you will need to do is add a column to the output that is going to contain the error description.

20050808ScriptComponentInputOutput.JPG

Here's the code from that script component:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper


Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Use the incoming error number as a parameter to GetErrorDescription
        Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub
End
Class

As you can see it just takes a simple line of code to get the error description. The ComponentMetadata object exposes many methods, including GetErrorDescription(), that can be useful to you in the script component so its worth spending time getting to know what it provides.

If you want to see this working without bothering to build it you can download the package from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050808GetErrorDescription.zip I've included the source file as well which you should place into the root of your C: drive..

Thanks go to Doug Laudenshlager for making me aware of this!

-Jamie

 

Published Monday, August 08, 2005 7:28 PM by jamie.thomson

Comments

 

Macaw - musings on SQLserver2005/SSIS/SSAS said:

Jamie Thomson wrote a good article about handling errors and getting a description of the errormessage...
August 9, 2005 10:33 AM
 

SimonS' SQL Blog said:

August 14, 2005 11:04 PM
 

SimonS said:

An extension to use GetErrorDescription with exceptions in Custom Components
August 14, 2005 11:07 PM
 

Wilfried Mausz said:

Hi!

Thanx a lot for your blog about getting the error description. One thing I still get not working is how to get the name of the column where the error occures in "row.ErrorColumn". Maybe you can help me?

Bye,

Wilfried
November 17, 2005 11:47 PM
 

David Russell said:

kewl beans! as they say in California :)

of course, the first message I got was

-1071607698,1207,No status is available.

But, still, thanks Jamie.
November 23, 2005 12:37 AM
 

simonsabin said:

Following on from Jamies post  on getting the error message from an error number. I wrote a help...
December 2, 2005 8:41 PM
 

Simon Sabin SQL Server Blog said:

Following on from Jamies post  on getting the error message from an error number. I wrote a help...
December 4, 2005 9:24 PM
 

SimonS said:

Following on from Jamies post  on getting the error message from an error number. I wrote a help...
February 9, 2006 1:00 AM
 

SimonS' SQL Server Stuff said:

Following on from Jamies post  on getting the error message from an error number. I wrote a help...
May 23, 2006 10:32 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems