Udostępnij za pośrednictwem

Enhancing an Error Output with the Script Component

  • Artykuł

New: 5 December 2005

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 IDTSComponentMetaData90 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. On the Input Columns page, select the ErrorCode column.

  5. 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.

  6. On the Script page, click Design Script to open the Visual Studio for Applications (VSA) scripting environment. In the Input0_ProcessInputRow method, type or paste the sample code shown below.

  7. Close the VSA IDE and the Script Transformation Editor.

  8. Attach the output of the Script component to a suitable destination. A Flat File destination is the easiest to configure for ad hoc testing.

  9. Run the package.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

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

End Sub

Change History

Release History

17 July 2006
Changed content:
  • Improved sequence of steps in examples.

See Also

Concepts

Using Error Outputs in a Data Flow Component
Creating a Synchronous Transformation with the Script Component

Other Resources

Handling Errors in Data

Help and Information

Getting SQL Server 2005 Assistance