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

BTW, if it's in a Data Flow, then it's a Script Component, not a Script Task. — John Saunders
– John Saunders, Commented Sep 29, 2009 at 3:40

Registered User · Accepted Answer · 2009-09-30 22:59:56Z

3

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.

answered Sep 30, 2009 at 22:59

Registered User

8,4238 gold badges54 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Glenn M Over a year ago

Thanks. As a follow up, I realise some confusion prevailed at my end. The popup error is actually desirable and probably necessary for the frontend to know what the problem is. I ditched all my Try Catch code in the script and let the package fail by itself, and when I ran it in the frontend the debug window in VS was replaced with a web error (which is OK) and at the backend the package continued with the OnError event (which cleans up some tables). Confusion came thru the need to click OK in VS to clear the error, but of course this is not a requirement when you run the package outside VS.

Registered User Over a year ago

You are right that removing the Try Catch block will allow failures to fail automatically. I ran into this issue about 1 month ago and came to the same conclusion. I came to the solution first by forcing an error in the Try Catch block. Then I added an error in the Catch block. As soon as I got this to force the failure, I realized the problem was the Try Catch block altogether. Let SSIS handle the exception instead of handling it yourself.

Stuart Steedman · Accepted Answer · 2011-01-12 17:05:41Z

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...

Stuart Steedman · Accepted Answer · 2011-03-09 10:10:10Z

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...

LCJ · Accepted Answer · 2015-12-09 20:49:16Z

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

Collectives™ on Stack Overflow

SSIS Dataflow script task error handling

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related