SSIS Dataflow script task error handling

ssis scripting handler dataflow onerror
Glenn M picture Glenn M · Sep 29, 2009 · Viewed 16.2k times · Source

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

Registered User picture Registered User · Oct 1, 2009

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.

Related questions

SSIS Execute Process Task Python script

I'm trying to execute a python scrip from SSIS Execute Process Task. I followed all the tutorials of how to do this an still the script is failing from the start. when i execute the python script out of SSIS …

Read More
Powershell returns negative exit code while script results are correct

I have made the following PowerShell script: Set-Location D:\folder1\folder2\folder3\folder4; Get-ChildItem | Rename-Item -NewName {$_.BaseName.insert(19,'loadtime') + (Get-Date -Format HHmm) + $_.Extension}; The goal is to rename a file by adding loadtime at position 19 and a time stamp. While …

Read More
How to pass variable as a parameter in Execute SQL Task SSIS?

I have ssis package in that I'm taking values from flat file and insert it into table. I have taken one Execute SQL Task in that creating one temptable CREATE TABLE [tempdb].dbo.##temptable ( date datetime, companyname nvarchar(50), price decimal(10,0), …

Read More