Saturday, April 3, 2010

Error handling using script component

This post is just to show how to trigger an event handler for a data flow task if any component inside the same data flow task fails. This approach will work only for those data flow task components which have error output (Merge, Merge Join etc.. has no error op).
Poblem: Suppose I have a lookup component inside Data flow task and any mismatch should be treated as error and an appropriate error message should be sent using Send Mail Task.
Solution: Take a data flow task and configure it using an OLEDB source, lookup component and take the error op of lookup to a script component. Check
here for error handling using script component. The data flow task would be like:
Create a string variable (in this case the name of variable is Error) and use this variable as ReadWriteVariables inside script component editor as shown below:

Use following code inside the script transformation editor (I am using VB.Net code):
Now create an event handler for the data flow task for "On Task Failed" event and add a Send Mail Task. For demo purpose I am using a script task (as I don't have smtp server configured) as shown below:

Value of variable (Error) can be verified by adding following piece of code inside scipt task editor (don't forget to set Error variable as ReadOnlyVariable inside Scrpt task editor):
Public Sub Main()
Dts.TaskResult = ScriptResults.Success

End Sub

1 comment:

  1. i enjoy most of your articles,the articles are so nice for readers.