SSIS Dataflow script task error handling

Question

Hi

I have a script task that is performing transformations in the middle of a SSIS dataflow. If the script fails (say it tries to convert alpha to numeric) I need it to stop with a 'failed' status and return to the main package and then utilise the Dataflow Task Event Handler OnError to exit gracefully.

At the moment I find that the script task in the dataflow returns a .net error popup which I have to then clear. I've tried a Try Catch around the code which seems to stop the debug window appearing but I can't seem to get it to exit the script with a 'failed status' that will cause the package to fail. The Dts.TaskResult = Dts.Results.Failure does not appear to be valid in dataflow tasks. At the moment I'm trying this:

    Catch e As System.Exception
        Me.ComponentMetaData.FireError(-1, "", "Error: ", e.Message, 1, True)
        While Not e.InnerException Is Nothing
            e = e.InnerException
            Me.ComponentMetaData.FireError(-1, "", "InnerException: ", e.Message, 1, True)
        End While
        Exit Sub
    End Try

... but all this does is skip the bad row. The dataflow continues. The problem is getting it to exit as 'failed' so the onError error handler event in the package is triggered.

Any suggestions gratefully received. Glenn

Answer 1

Script Transformations don't have the same features for returning success or failure. You can force an error by using this code:

    If Row.TestColumn = "Value I Want To Error On" Then
        Error (1)
    End If

Basically, the Error object (function? method? whatever!) will allow you to simulate an error. Meaning, you can make the package error with this code.