Home > SSIS Best Practices > SSIS – Setting Options in Error Output

SSIS – Setting Options in Error Output

Many data flow components in SSIS allow you to set options when an error or trucnation occurs. The options you can set for Error or Truncation are:

  • Fail Component
  • Ignore Error
  • Redirect Row

You would set it to Redirect Row if you want to use a Script Transformation component to write the error into a destination.

Define 3 columns for the  Script Transformation component: Error Source Column, ErrorCode, ErrorColumn

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)
        Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    End Sub

End Class

An error output example:

100ty
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

200yu
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

300rr
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: