SSIS: Capture data conversion error messages using script component

Enhancing an Error Output with the Script Component

I am redirecting rows from a flat file source to a sql database. In the middle I am converting the data types and I want to capture the error message during the data conversion, why the conversion failed?

Part of the question (adding the error description) can be achieved with a script component. This is described in Enhancing an Error Output with the Script Component.

The scenario is:

Flat source file–>Data conversion-error output–>script component. Then in the script component, in the input columns section, you will see two default column ErrorCode and ErrorColumn.

 

To configure this Script Component example

  1. Before creating the new Script component, configure an upstream component in the data flow to redirect rows to its error output when an error or truncation occurs. For testing purposes, you may want to configure a component in a manner that ensures that errors will occur—for example, by configuring a Lookup transformation between two tables where the lookup will fail.
  2. Add a new Script component to the Data Flow designer surface and configure it as a transformation.
  3. Connect the error output from the upstream component to the new Script component.
  4. Open the Script Transformation Editor, and on the Script page, for the ScriptLanguage property, select the script language.
  5. Click Edit Script to open the Microsoft Visual Studio Tools for Applications (VSTA) IDE and add the sample code shown below.
  6. Close VSTA.
  7. In the Script Transformation Editor, on the Input Columns page, select the ErrorCode column.
  8. On the Inputs and Outputs page, add a new output column of type String named ErrorDescription. Increase the default length of the new column to 255 to support long messages.
  9. Close the Script Transformation Editor.
  10. Attach the output of the Script component to a suitable destination. A Flat File destination is the easiest to configure for ad hoc testing.
  11. Run the package.
public class ScriptMain:
    UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

  Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    }
}

 

 

 

 

Advertisements

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