Error rows, Code and Description in SSIS

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 ONLY  'Error Code" and 'ErrorColumn'., add an OUTPU column to hold the error description.

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
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub
End Class

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.

Hope that helps someone...