Wednesday, January 10, 2007

SSIS : Creating an error output from a script component

I recently had the need to create an error output from a script component and thought I would create a blog to explain how easily it can be done.

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

Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Try

Row.SalesWithoutDiscount
= CDec(Row.SalesAmount) / CDec(Row.DiscountAmount)

' Redirect rows toward valid row output

Row.DirectRowToValidRows()

Catch ex As Exception

' Capture error description

Row.ErrorDescription
= ex.Message.ToString

' Redirect rows toward error row output

Row.DirectRowToErrorRows()

End Try

End Sub

End Class


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.

7 comments:

Anonymous said...

Just wanted to let you know that this was extremely helpful with some problems I have been having.

Thanks

Colin Kirkby said...

Very good to hear. Thanks for the feedback!

Anonymous said...

Very useful post ...

Drackbolt said...

Nice, but what exactly goes inside "DirectRowToErrorRows"?

Colin Kirkby said...

Hi Drackbolt,
The DirectRowToErrorRows is actually a method that is linked to the ErrorRows output. So you wouldn't necessarily, in this example, put anything inside it but rather set the values for the row output columns e.g. Row.ErrorDescription = "Some error" and then call the method so that those rows flow through the ErrorRows output. I hope this explanation makes sense.
Regards,
Colin.

Drackbolt said...

Thanks for the feedback. I couldn't get this method to recognize in VS 2005 though... At any rate it doesn't matter any longer as I figured out what the synchronousinput property actually does. :)

Guru said...

Thank You.
Extremely helpful... was struggling to configure for last 4 hours