Edit

Share via

Coding a Custom Task

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

After you have created a class that inherits from the Microsoft.SqlServer.Dts.Runtime.Task base class, and applied the DtsTaskAttribute attribute to the class, you must override the implementation of the properties and methods of the base class to provide your custom functionality.

Configuring the Task

Validating the Task

When you are designing an Integration Services package, you can use validation to verify settings on each task, so that you can catch incorrect or inappropriate settings as soon as they are set, instead of finding all errors at run-time only. The purpose of validation is to determine whether the task contains invalid settings or connections that will prevent it from running successfully. This makes sure that the package contains tasks that have a good chance of running on their first run.

You can implement validation by using the Validate method in custom code. The run-time engine validates a task by calling the Validate method on the task. It is the responsibility of the task developer to define the criteria that give you a successful or failed task validation, and to notify the run-time engine of the result of this evaluation.

Task Abstract Base Class

The Microsoft.SqlServer.Dts.Runtime.Task abstract base class provides the Validate method that each task overrides to define its validation criteria. The SSIS Designer automatically calls the Validate method multiple times during package design, and provides visual cues to the user when warnings or errors occur to help identify problems with the configuration of the task. Tasks provide validation results by returning a value from the DTSExecResult enumeration, and by raising warning and error events. These events contain information that is displayed to the user in SSIS Designer.

Some examples for validation follow:

  • A connection manager validates the specific file name.

  • A connection manager validates that the type of input is the expected type, such as an XML file.

  • A task that expects database input verifies that it cannot receive data from a non-database connection.

  • A task guarantees that none of its properties contradicts other properties set on the same task.

  • A task guarantees that all required resources used by the task at execution time are available.

Performance is something to consider in determining what is validated and what is not. For example, the input to a task might be a connection over a network that has low bandwidth or heavy traffic. The validation may take several seconds to process if you decide to validate that the resource is available. Another validation may cause a round-trip to a server that is in high demand, and the validation routine might be slow. Although there are many properties and settings that can be validated, not everything should be validated.

  • The code in the Validate method is also called by the TaskHost before the task is run, and the TaskHost cancels execution if validation fails.

User Interface Considerations during Validation

The Microsoft.SqlServer.Dts.Runtime.Task includes an IDTSComponentEvents interface as a parameter to the Validate method. The IDTSComponentEvents interface contains the methods that are called by the task in order to raise events to the run-time engine. The FireWarning and FireError methods are called when a warning or error condition occurs during validation. Both warning methods require the same parameters, which include an error code, source component, description, Help file, and Help context information. The SSIS Designer uses this information to display visual cues on the design surface. The visual cues that are provided by the designer include an exclamation icon that appears next to the task on the designer surface. This visual cue signals to the user that the task requires additional configuration before execution can continue.

The exclamation icon also displays a ToolTip that contains an error message. The error message is provided by the task in the description parameter of the event. Error messages are also displayed in the Task List pane of SQL Server Data Tools (SSDT), providing the user with a central location for viewing all validation errors.

Validation Example

The following code example shows a task with a UserName property. This property has been specified as required for validation to succeed. If the property is not set, the task posts an error, and returns Failure from the DTSExecResult enumeration. The Validate method is wrapped in a try/catch block, and fails validation if an exception occurs.

using System;  
using Microsoft.SqlServer.Dts.Runtime;  
  
public class SampleTask : Task  
{  
  private string userName = "";  
  
  public override DTSExecResult Validate(Connections connections,  
     VariableDispenser variableDispenser, IDTSComponentEvents events,  
     IDTSLogging log)  
  {  
    try  
    {  
      if (this.userName == "")  
      {  
        //   Raise an OnError event.  
        events.FireError(0, "SampleTask", "The UserName property must be configured.", "", 0);  
        //   Fail validation.  
        return DTSExecResult.Failure;  
      }  
      //   Return success.  
      return DTSExecResult.Success;  
    }  
    catch (System.Exception exception)  
    {  
      //   Capture exceptions, post an error, and fail validation.  
      events.FireError(0, "Sampletask", exception.Message, "", 0);  
      return DTSExecResult.Failure;  
    }  
  }  
  public string UserName  
  {  
    get  
    {  
      return this.userName;  
    }  
    set  
    {  
      this.userName = value;  
    }  
  }  
}  

Imports System  
Imports Microsoft.SqlServer.Dts.Runtime  
  
Public Class SampleTask  
  Inherits Task  
  
  Private _userName As String = ""  
  
  Public Overrides Function Validate(ByVal connections As Connections, _  
     ByVal variableDispenser As VariableDispenser, _  
     ByVal events As IDTSComponentEvents, _  
     ByVal log As IDTSLogging) As DTSExecResult  
  
    Try  
      If Me._userName = "" Then  
        '   Raise an OnError event.  
        events.FireError(0, "SampleTask", "The UserName property must be configured.", "", 0)  
        '   Fail validation.  
        Return DTSExecResult.Failure  
      End If  
      '   Return success.  
      Return DTSExecResult.Success  
    Catch exception As System.Exception  
      '   Capture exceptions, post an error, and fail validation.  
      events.FireError(0, "Sampletask", exception.Message, "", 0)  
      Return DTSExecResult.Failure  
    End Try  
  
  End Function  
  
  Public Property UserName() As String  
    Get  
      Return Me._userName  
    End Get  
    Set(ByVal Value As String)  
      Me._userName = Value  
    End Set  
  End Property  
  
End Class

Persisting the Task

Ordinarily you do not have to implement custom persistence for a task. Custom persistence is required only when the properties of an object use complex data types. For more information, see Developing Custom Objects for Integration Services.

Executing the Task

This section describes how to use the Execute method that is inherited and overridden by tasks. This section also explains various ways of providing information about the results of task execution.

Execute Method

Tasks that are contained in a package run when the Integration Services runtime calls their Execute method. Tasks implement their core business logic and functionality in this method, and provide the results of execution by posting messages, returning a value from the DTSExecResult enumeration, and overriding the property get of the ExecutionValue property.

The Microsoft.SqlServer.Dts.Runtime.Task base class provides a default implementation of the Execute method. The custom tasks override this method to define their run-time functionality. The TaskHost object wraps the task, isolating it from the run-time engine and the other objects in the package. Because of this isolation, the task is unaware of its location in the package with regard to its execution order, and runs only when it is called by the runtime. This architecture prevents problems that can occur when tasks modify the package during execution. The task is provided access to the other objects in the package only through the objects supplied to it as parameters in the Execute method. These parameters let tasks raise events, write entries to the event log, access the variables collection, and enlist connections to data sources in transactions, while still maintaining the isolation that is necessary to guarantee the stability and reliability of the package.

The following table lists the parameters provided to the task in the Execute method.

Parameter Description
Connections Contains a collection of ConnectionManager objects available to the task.
VariableDispenser Contains the variables available to the task. The tasks use variables through the VariableDispenser; the tasks do not use variables directly. The variable dispenser locks and unlocks variables, and prevents deadlocks or overwrites.
IDTSComponentEvents Contains the methods called by the task to raise events to the run-time engine.
IDTSLogging Contains methods and properties used by the task to write entries to the event log.
Object Contains the transaction object that the container is a part of, if any. This value is passed as a parameter to the AcquireConnection method of a ConnectionManager object.

Providing Execution Feedback

Tasks wrap their code in try/catch blocks to prevent exceptions from being raised to the run-time engine. This ensures that the package finishes execution and does not stop unexpectedly. However, the run-time engine provides other mechanisms for handling error conditions that can occur during the execution of a task. These include posting error and warning messages, returning a value from the DTSExecResult structure, posting messages, returning the DTSExecResult value, and disclosing information about the results of task execution through the ExecutionValue property.

The IDTSComponentEvents interface contains the FireWarning and FireError methods, which can be called by the task to post error and warning messages to the run-time engine. Both methods require parameters such as the error code, source component, description, Help file, and help context information. Depending on the configuration of the task, the runtime responds to these messages by raising events and breakpoints, or by writing information to the event log.

The TaskHost also provides the ExecutionValue property that can be used to provide additional information about the results of execution. For example, if a task deletes rows from a table as part of its Execute method, it might return the number of rows deleted as the value of the ExecutionValue property. In addition, the TaskHost provides the ExecValueVariable property. This property lets the user map the ExecutionValue returned from the task to any variable visible to the task. The specified variable can then be used to establish precedence constraints between tasks.

Execution Example

The following code example demonstrates an implementation of the Execute method, and shows an overridden ExecutionValue property. The task deletes the file that is specified by the fileName property of the task. The task posts a warning if the file does not exist, or if the fileName property is an empty string. The task returns a Boolean value in the ExecutionValue property to indicate whether the file was deleted.

using System;  
using Microsoft.SqlServer.Dts.Runtime;  
  
public class SampleTask : Task  
{  
  private string fileName = "";  
  private bool fileDeleted = false;  
  
  public override DTSExecResult Execute(Connections cons,  
     VariableDispenser vars, IDTSComponentEvents events,  
     IDTSLogging log, Object txn)  
  {  
    try  
    {  
      if (this.fileName == "")  
      {  
        events.FireWarning(0, "SampleTask", "No file specified.", "", 0);  
        this.fileDeleted = false;  
      }  
      else  
      {  
        if (System.IO.File.Exists(this.fileName))  
        {  
          System.IO.File.Delete(this.fileName);  
          this.fileDeleted = true;  
        }  
        else  
          this.fileDeleted = false;  
      }  
      return DTSExecResult.Success;  
    }  
    catch (System.Exception exception)  
    {  
      //   Capture the exception and post an error.  
      events.FireError(0, "Sampletask", exception.Message, "", 0);  
      return DTSExecResult.Failure;  
    }  
  }  
  public string FileName  
  {  
    get { return this.fileName; }  
    set { this.fileName = value; }  
  }  
  public override object ExecutionValue  
  {  
    get { return this.fileDeleted; }  
  }  
}  

Imports System  
Imports Microsoft.SqlServer.Dts.Runtime  
  
Public Class SampleTask  
  Inherits Task  
  
  Private _fileName As String = ""  
  Private _fileDeleted As Boolean = False  
  
  Public Overrides Function Execute(ByVal cons As Connections, _  
     ByVal vars As VariableDispenser, ByVal events As IDTSComponentEvents, _  
     ByVal log As IDTSLogging, ByVal txn As Object) As DTSExecResult  
  
    Try  
      If Me._fileName = "" Then  
        events.FireWarning(0, "SampleTask", "No file specified.", "", 0)  
        Me._fileDeleted = False  
      Else  
        If System.IO.File.Exists(Me._fileName) Then  
          System.IO.File.Delete(Me._fileName)  
          Me._fileDeleted = True  
        Else  
          Me._fileDeleted = False  
        End If  
      End If  
      Return DTSExecResult.Success  
    Catch exception As System.Exception  
      '   Capture the exception and post an error.  
      events.FireError(0, "Sampletask", exception.Message, "", 0)  
      Return DTSExecResult.Failure  
    End Try  
  
  End Function  
  
  Public Property FileName() As String  
    Get  
      Return Me._fileName  
    End Get  
    Set(ByVal Value As String)  
      Me._fileName = Value  
    End Set  
  End Property  
  
  Public Overrides ReadOnly Property ExecutionValue() As Object  
    Get  
      Return Me._fileDeleted  
    End Get  
  End Property  
  
End Class

See Also

Creating a Custom Task
Coding a Custom Task
Developing a User Interface for a Custom Task