SSIS Error handling

Check the attached screen shots, Sorry I will explain it later, I need to run to now.
error.png
error2.png
error3.png
error4.png

You can redirect error rows in data flow. If you want to capture them then you need to evaluate rows and redirect in data flow and at the same time you can provide error message.

Regards
Emil

OK, sorry for not explaining earlier...I needed to go to a meeting.
Anyway...

1-double click the OLE DB destination to bring it's properties.

2-go to "Error Output" as highlighted in the screenshot, "select "Redirect Row" from the drop-down menu. that will redirect the bad rows to the output.

3-Add a 'script Component task' , go to the input columns, select 'Error Code" and 'ErrorColumn'

4-Go to the script screen, and add the following script, make sure to US VISUAL BASIC 2008 as your input language.

5-paste that script..
 

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

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
        Inherits UserComponent
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
        End Sub

    End Class
End Class

Select allOpen in new window

6- now you'll have 3 columns, the error code, error row and the description of the error in english.

7-connect a FLAT FILE DESTINATION to the script component so you can captuure the oputput to text, or connect another OLE DB DESTINATION to collect to a database.

Sorry typo in the script above, use this one.

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

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub

End Class

Select allOpen in new window

huslayer,
Thanks for quick response.
My source  SQL table has Fname(char),Lname(Char),SSN(CHAR).
My destination  SQL table has Fname(char),Lname(Char),SSN(int).
My intension is to capture Error rows and error description and show to user or load in table.

I have worked as per your guidance. But I am getting error  at script task
 "
   at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)".

Conversion from string " The data value can not convert " to type int is not valid
I am attaching my schemas,error message.  It is bit urgent. Can you please  guide me ?

source-design.png
destination-schema.png
dataflow-design.png
dataflow-errormessage.png

In the  'script Component task' , go to the input columns, select ONLY  'Error Code" and 'ErrorColumn'

then use this screen shot 5 to create an output column.

Hi,
I did changes as per your suggestion.
I am getting error in script task " Index was outside the array boundary ".

   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.Input0Buffer.get_ErrorCode()
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Please find attached screenshots.
I have to capture error data rows  ,error description  and show to user or inser to table. I am new to SSIS.
Thanks for your help.
script-Error.png
input-Columns.png
OutputCols.png

My input data is as in screen shot.
SOURCE-DATA.png

Most likely the Error_Description column data type is wrong, compare what you did with screen shot 5, make sure it's a string.

give me screen shot of that column configuration and the script task script if it didn't work with you.

Here is the script in Script task :

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

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

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        '
        ' Add your code here for preprocessing or remove if not needed
        '
    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        '
        ' Add your code here for postprocessing or remove if not needed
        ' You can set read/write variables here, for example:
        ' Me.Variables.MyIntVar = 100
        '
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        '
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    End Sub

End Class


Find attached screenshot of Column configuration.
 I am  getting same error .
"I am getting error in script task " Index was outside the array boundary ".

   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.Input0Buffer.get_ErrorCode()
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
   at SC_4fa86d8dc1f345278e92da8fb2778eee.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

"

Thanks in advance for your help. Hope you will be able to figure out where I am doing wrong. Let me know if you need any more details.
InputColConfig.png
InputOutputColConfig.png

ok, delete everything inside the script, and only what I've provided above....

replace mine with everything in the script in other words.

I did not get you. Can you explain again?

you have wrong extra code in the script task, delete all and only put my script

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

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub

End Class

Select allOpen in new window

Sorry to bother you. I have replaced my code with your above code. I am getting error.
" The value is too long to put in column data area of buffer".
Please find attached screenshot. I am not able to figure out  where I am going wrong.Please guide  me
script-error1.png

huslayer,
I got solution. Have to increase the output column length in script task . Its working fine  now. I have a question .Is there anyway to show error message to user in UI ?

how the users will run the package ?

How to capture which column is causing error ?  I have implemented as per your suggestion. It works fine
But  in general data load fails because of column " SSN" .
" [OLE DB Destination [16]] Error: There was an error with input column "SSN" (65) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
"
This is the error message usually we get when there is destination column mismatch with source column. I want to capture this error description  and load into the table. What ever error messages you get in Execution results tab , I want to capture and load to table for analyss.Can you please suggest on this ?
error-description.png

error rows should go to the output, use a sql table or flat file destination and it'll hold the failed rows, along with the error description, isn't that what we were doing?

Anyway you should implment your own logic to send these rows to a hold table or fix it using a derived column.

attach your package, let me look at it..

We are capturing error description in above example. I want to capture column that is causing error .

To be more clear --How to Find Out Which Column Caused SSIS to Fail?

Our goal is to capture Error column , Error description, Table name and show to business users in UI using web application. So that the users can correct the bad data .
So we want to capture the error column ,error description of the bad data that causes the package to fail at any point during execution.

Please find attached package. Could you please guide on this ?
ErrorHandlingSSIS.sln

partial