• Article

Enhancing an Error Output with the Script Component

By default, the two extra columns in an Integration Services error output, ErrorCode and ErrorColumn, contain only numeric codes that represent an error number, and the ID of the column in which the error occurred. These numeric values may be of limited use without the corresponding error description.

This topic describes how to add an error description column to existing error output data in the data flow by using the Script component. The example adds the error description that corresponds to a specific predefined Integration Services error code by using the GetErrorDescription method of the IDTSComponentMetaData100 interface, available through the ComponentMetaData property of the Script component.

Note

If you want to create a component that you can more easily reuse across multiple Data Flow tasks and multiple packages, consider using the code in this Script component sample as the starting point for a custom data flow component. For more information, see Developing a Custom Data Flow Component.

Example

The example shown here uses a Script component configured as a transformation to add an error description column to existing error output data in the data flow.

For more information about how to configure the Script component for use as a transformation in the data flow, see Creating a Synchronous Transformation with the Script Component and Creating an Asynchronous Transformation with the Script Component.

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
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

  Row.ErrorDescription = _
    Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    End Sub
End Class

public class ScriptMain:
    UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

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

    }
}
Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.