SSIS Dataflow script task error handling

Question

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.

Answer 2

I've been searching for a while to the answer to this question. The popup error is just too annoying for me! To avoid this, a "simple" solution (hack) is this:

Rather than throw an error after the .FireError, create a new DT_UI1 output column in the script transformation, e.g. "ValidationColumn", and set it to 1 or 0 (Not Boolean for reasons that will become clear).

Immediately after the script component, add a derived column transformation, and replace the ValidationColumn with the formula: 1/ValidationColumn. (This does not work with Boolean). This, of course, generateds a divide by zero error, and (using the default setting) fails the derived column transformation and thus immediately the data flow component. Voila!

The error log has the original validation failed message from the .FireError, immediately followed by a divide by zero error.

This may be a hack, but until someone comes up with a better idea...

BTW, I am using this to check that Excel files have the correct headers in the correct place (or alternative places), in conjuction with using IMEX=1, in order to load 2 or more different column variations using a single data flow...

Answer 3

In retrospect, the divide by zero error is not necessary.

In my current solution, I am capturing the error, then doing a FireError, then reimplementing the exception handling, like this:

If excludeHeader = -1 Then
    'Throw New InvalidDataException("Invalid exclude column: " & Variables.excludeColumn)
    ComponentMetaData.FireError(0, ComponentMetaData.Name.Trim(), "Invalid exclude column: " & Variables.excludeColumn, String.Empty, 0, True)
    excelConnection.Close()
    excelConnection.Dispose()
    Return
End If

This works because it is contained within a Source Script, and would also work in a Transformation Script providing the data flow was set to fail after 1 error. If not, the script would need to implement an error output path which, frankly, I don't have time for...

Answer 4

Following is a script task I created inside a loop. It is not a direct answer to your question – but the overall idea will help.

The script task is kept inside a sequence container. And the sequence container’s variable named Propagate is set as false. Also, for the sequence container the MaximumErrorCount property is set as zero. So, when an error happen inside the sequence container it is shown in red, OnError event fired – but the loop is continued. It is important to create an onerror event handler for the sequence container for this to work.

Inside the script task, it is forcefully failed inside catch block (by setting task result as Failure). Also the exception message is stored in a variable for storing it into the error logging table. This error data insertion happens from the OnError event handler’s (mentioned above) execute sql task.

Refer: MSDN - ScriptObjectModel.TaskResult Property

Use the TaskResult property of the Dts object in Script task code to notify the package of the success or failure of the Script task.

The catch block inside the script task looks like the one listed below.

  Catch ex As Exception

        Dim exceptionVariable As Microsoft.SqlServer.Dts.Runtime.Variables = Nothing
        Dts.VariableDispenser.LockOneForWrite("User::ScriptException", exceptionVariable)
        exceptionVariable("User::CustomScriptException").Value = ex.Message
        exceptionVariable.Unlock()
        Dts.Events.FireError(-1, "Task Name", ex.Message, [String].Empty, 0)
        Dts.TaskResult = Dts.Results.Failure
  End Try

Control Flow