Errors in script tasks and components

Unhandled errors thrown out of SSIS script tasks are usually accompanied by the message “Exception has been thrown by the target of an invocation” – this isn't very informative!

A more manageable approach is to wrap as much of your code as possible inside a try/catch, then raise caught errors to SSIS for cleaner failure and easier diagnosis. This article shows you how to do that.

Script tasks are implemented in the Main() method of an auto-generated ScriptMain class1). If you're used to writing console apps in C# you'll notice that this Main() is an instance method (i.e. it's not static).

Below is a pattern for implementing Main(). The pattern:

  • uses a try block to catch any exception raised within it;
  • in the corresponding catch block, raises the error to SSIS using the Dts.Events.FireError method.
    public void Main()
        // *** START OF YOUR TASK CODE ***
        // ...
        // *** END OF YOUR TASK CODE ***
        Dts.TaskResult = (int)ScriptResults.Success;            // Got this far? Success!
      catch (Exception e)
        Dts.Events.FireError(-1, "Main()", e.Message, "", -1);  // Raise the error event to SSIS,
        Dts.TaskResult = (int)ScriptResults.Failure;            // and report the task failed.

I implement every script task body like this.

The code assumes that the ScriptMain class defines the ScriptResults enumeration – this is part of the code generated automatically for ScriptMain.

Note that errors raised before the ScriptMain object is instantiated can't report errors this way, because the Dts property is non-static.

You don't need to to worry about that unless you're doing something unusual (e.g. overloading the class's static constructor to load external assemblies at runtime).

For copy/paste.

    public void Main()
        Dts.TaskResult = (int)ScriptResults.Success;
      catch (Exception e)
        Dts.Events.FireError(-1, "Main()", e.Message, "", -1);
        Dts.TaskResult = (int)ScriptResults.Failure;

I don't write script tasks in anything except C#, but sometimes you have to support existing SSIS packages containing tasks in VB.Net. This equivalent allows you to strengthen your diagnostics without having to reimplement the whole thing right away.

   Public Sub Main()
        ' *** START OF USER CODE ***
        ' ...
        ' *** END OF USER CODE ***
        Dts.TaskResult = ScriptResults.Success
      Catch e As Exception
         Dts.Events.FireError(-1, "Main()", e.Message, "", -1)
         Dts.TaskResult = ScriptResults.Failure
      End Try
   End Sub

Transformation script components have a number of methods that can contain user code:

  • PreExecute() is called before any rows pass through the component;
  • PostExecute() is called after all rows have passed through the component.

Other methods are provided for per-row processing – in the simplest case (one input, default name accepted), the method Input0_ProcessInputRow() is called to process each row passing through the transformation.

The body of each method should be protected by a try/catch as in the case of a script task's Main() method, but in this case errors are raised using the ComponentMetaData property:

  public override void Input0_ProcessInputRow(Input0Buffer Row)
      // *** START OF YOUR CODE ***
      // *** END OF YOUR CODE ***
    catch (Exception e)
      bool cancel = false;  // set to true to cause execution to abort
      ComponentMetaData.FireError(-1, "Input0_ProcessInputRow()", e.Message, "", -1, out cancel);  // raise the error event to SSIS

Source and destination script components are a less frequent requirement, but you can take exactly the same approach to manage errors thrown from their methods.

A subclass of Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase.
San, 2021/06/04 18:33
thanks a lot, I could not find the script transformation error handling. your blog post saved me a ton of time :)
day="$(date + '%a')"
echo $day

if [ "$day" = "$bigweek"]; then
T1_DATE=$(date + '%Y%m%d' -d "-4 day")
T_DATE=$(date + '%Y%m%d' -d "-3 day")

T1_DATE=$(date + '%Y%m%d' -d "-2 day")
T_DATE=$(date + '%Y%m%d' -d "-1 day")