I have created a simple data flow with a OLEDB source that uses SalesAmount and DiscountAmount from the AdventureWorksDW FactResellerSales Table as source inputs. I choice these columns as Discount has values with zeros in it and I wanted to force a divide by zero error. The data flows into the Script component, it then divides the two columns and redirects the errors to the Error Rowcount destination. The non error rows are directed to the valid rows Rowcount destination.
When you first create a script component as a transformation, it defaults to having a single input and single output. For this example I have added an extra script output (ErrorRows) and renamed the existing script output to ValidRows. I added an Outputcolumn to the ErrorRows script output called ErrorDescription. I then changed the SynchronousInput property of both script outputs to the script input's ID property. I also changed the two script output ExclusionGroup properties to be 1.
The rest is made easy with the following code:
Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Pipeline.Wrapper, Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Row.SalesWithoutDiscount = CDec(Row.SalesAmount) / CDec(Row.DiscountAmount)
' Redirect rows toward valid row output
Catch ex As Exception
' Capture error description
Row.ErrorDescription = ex.Message.ToString
' Redirect rows toward error row output
The Data Viewers show the progress when the package is executed. The errors have been redirected to the Error Rows Row Count Destination. The ErrorDescription output column has been populated with the Error Message - "Attempted to Divide By Zero." as expected.
This can be greatly extended to have multpile output all being directed to the destinations of your choice. I, however, always include one output that will deal with any error rows that may result from the script component.