Simulating an Error Output for the Script Component
New: 5 December 2005
Although you cannot directly configure an output as an error output in the Script component for automatic handling of error rows, you can reproduce the functionality of a built-in error output by creating an additional output and using conditional logic in your script to direct rows to this output when appropriate. You may want to imitate the behavior of a built-in error output by adding two additional output columns to receive the error number and the ID of the column in which an error occurred.
If you want to add the error description that corresponds to a specific predefined Integration Services error code, you can use the GetErrorDescription method of the IDTSComponentMetaData90 interface, available through the Script component's ComponentMetaData property.
Example
The example shown here uses a Script component configured as a transformation that has two synchronous outputs. The purpose of the Script component is to filter error rows from address data in the AdventureWorks sample database. This fictitious example assumes that we are preparing a promotion for North American customers and need to filter out addresses that are not located in North America.
To configure the example
Before creating the new Script component, create a connection manager and configure a data flow source that selects address data from the AdventureWorks sample database. For this example, which only looks at the CountryRegionName column, you can simply use the Person.vStateCountryProvinceRegion view, or you can select data by joining the Person.Address, Person.StateProvince, and Person.CountryRegion tables.
Add a new Script component to the Data Flow designer surface and configure it as a transformation. Open the Script Transformation Editor.
On the Input Columns page, select the columns that you want to process in the Script transformation. This example uses only the CountryRegionName column. Available input columns that you leave unselected will simply be passed through unchanged in the data flow.
On the Inputs and Outputs page, add a new, second output, and set its SynchronousInputID value to the ID of the input, which is also the value of the SynchronousInputID property of the default output. Set the ExclusionGroup property of both outputs to the same non-zero value (for example, 1) to indicate that each row will be directed to only one of the two outputs. Give the new error output a distinctive name, such as "My Error Output."
Add additional output columns to the new error output to capture the desired error information, which may include the error code, the ID of the column in which the error occurred, and possibly the error description. This example creates the new columns ErrorColumn and ErrorMessage. If you are catching predefined Integration Services errors in your own implementation, make sure to add an ErrorCode column for the error number.
Note the ID value of the input column or columns that the Script component will check for error conditions. This example uses this column identifier to populate the ErrorColumn value.
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.
Close the VSA IDE and the Script Transformation Editor.
Attach the outputs of the Script component to suitable destinations. Flat file destinations are the easiest to configure for ad hoc testing.
Run the package.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.CountryRegionName <> "Canada" _
And Row.CountryRegionName <> "United States" Then
Row.ErrorColumn = 68 ' ID of CountryRegionName column
Row.ErrorMessage = "Address is not in North America."
Row.DirectRowToMyErrorOutput()
Else
Row.DirectRowToOutput0()
End If
End Sub
See Also
Concepts
Using Error Outputs in a Data Flow Component
Creating a Synchronous Transformation with the Script Component