====== Errors in script tasks and components ======

{{tag> blog ssis script_tasks}}

<WRAP group>
<WRAP 35% column>
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.
</WRAP>
<WRAP 35% column>
{{:ssis:scripttaskerror.png?nolink&350|}}
</WRAP>
</WRAP>


===== Script tasks =====

Script tasks are implemented in the ''Main()'' method of an auto-generated ''ScriptMain'' class((A subclass of ''Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase''.)). 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.

<code csharp>
    public void Main()
    {
      try
      {
        // *** 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.
      }
    }
</code>
<WRAP group>
<WRAP 40% column>
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''.
</WRAP>
<WRAP 50% column>
<WRAP center round tip 100%>
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 [[loading external assemblies|load external assemblies]] at runtime).
</WRAP></WRAP></WRAP>

==== Comment-free ====
For copy/paste.
<code csharp>
    public void Main()
    {
      try
      {


        Dts.TaskResult = (int)ScriptResults.Success;
      }
      catch (Exception e)
      {
        Dts.Events.FireError(-1, "Main()", e.Message, "", -1);
        Dts.TaskResult = (int)ScriptResults.Failure;
      }
    }
</code>

==== In VB.NET ====
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.

<code vbnet>   Public Sub Main()

      Try
        ' *** 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
</code>

===== Script components =====

//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 tasks|script task]]'s ''Main()'' method, but in this case errors are raised using the ''ComponentMetaData'' property:

<code csharp>  public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
    try
    {
      // *** 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
    }

  }
</code>

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

~~DISCUSSION:closed~~