Custom Task Best Practices

To this point, the discussion has centered on programming details. However, you should also take a number of design considerations into account while developing a custom task. They are hard-learned lessons codified here as best practices after experiences with developing a number of custom tasks as well as designing most of the stock tasks that ship in the SQL Server box. The practices are organized in no particular order.

Conventions and Constraints

Although building a no-op custom task is trivial, you should follow a few guidelines when writing a fully functional custom task so that the task supports the features and facilities found in the SSIS runtime. Following these guidelines ensures that a custom task will play nice in the SSIS sandbox.

Do Not Retain Internal State

Don't retain state in your task after returning from the Execute method. What does that mean? You have properties right, and that's state? It is possible for Execute to be called multiple times per one instance of a task. If this happens and internal state is retained between executions, the task might behave differently even if all its properties have the same values. This is nondeterministic behavior and is undesirable.

It's important that a task's behavior is solely determined by its internal logic based on exposed read/write properties. In other words, if you have a property value that represents state and that should be retained across instances or executions, it should be a writable property that can only be changed externally.

Do Not Internally Change Writable Property Values

The second rule is a corollary to the first. Don't internally change writable property values. If the property is writable, it is an indication that it is to be set by the user. Changing it could cause unexpected, even damaging behavior. If you have state that you want to expose to the user and the state is volatile due to internal functions of the task, make it a read-only property. This indicates to the user that the property is for information only and does not control or affect execution of the task.

Do Use Logging

This is very important. If the custom task doesn't log progress, errors, warnings, information, or other events, the user will have little in the way of troubleshooting the task when things go sideways with the task. Logging makes it possible to clearly indicate what the task is doing at any time. If you are concerned about performance, understand that logging is turned off by default and you can also check to see if logging is enabled before even attempting to log.

Always Exit Gracefully

Tasks should always exit their methods from the same location, ensuring that they have cleaned up resources and returned the correct execution result. For managed tasks, that means using try/catch blocks around any potentially failing code, especially the Validate and Execute methods. If code in the task generates an exception during validation or execution, it should log the error and/or raise an OnError or OnWarning event with descriptions of the errors. Tasks that are unable to recover due to a failure should indicate that the failure occurred by returning DTSER_FAILED for their execution result. Tasks that raise exceptions are considered an exceptional case and cause the package to shut down.

Honor the InteractiveMode System Variable

If your custom task shows any sort of UI during execution time, it should check the InteractiveMode system variable. If the package is executing, and the InteractiveMode system variable is set to FALSE, the task should not show the UI. Most tasks don't show UI at all unless their taskUI is launched, so this is rare.

Do Not Choose Taskhost Conflicting Names

Care should be taken to avoid property names that conflict with names found on the Taskhost. For example, if the name "Description" is used for a property on a task, it conflicts with the Description property found on task hosts and is hidden in the designer. To avoid confusion, the following names should not be used for properties on tasks:

• Name

• Description

• CreationName

• ID

Task UI

A task writer is not required to create a task UI to accompany his task. However, it is highly recommended. It is practically mandatory if you plan to sell the task. Tasks that do not provide a task UI are only editable using the property grid in the designer. For very simple tasks, this might be acceptable, especially for internal-only tasks. For tasks with several properties or having even mildly complex settings, a task UI is essential.

List Resources

When listing resources in list boxes or combo boxes, the taskUI should only show valid options. Two examples are list boxes that show Variables or Connection Managers.

Filter Variables

Filter out read-only variables when showing variables that the package will write to and only display variables that are in scope.

Filter Connection Managers

Connection managers should be filtered by type required. For example, if the task requires a file connection manager, the taskUI should only show the available file connection managers in the package.

Correctly Name Log Events

Log event names should be named by combining the task's type name with the log event. For example, if the task is a SQL Task and the log event was SQL Statement Processed, the log event name should be SQLTaskSQLStatementProcessed.

Log event names should use proper case and no spaces. Log events should not use abbreviated words nor should they be prefixed. It is common to prefix log entries with common prefixes such as On, Log, Event, or LogEvent. These are usually redundant and add to the log entry name length without adding uniqueness.

Use Custom Breakpoints Wisely

Breakpoints should only be implemented if there is a good reason, for example, if a task performs multiple, repeated operations or executes for a long period of time doing variable operations with external side effects. The user might be interested in understanding how the task settings impact the side effects. Breakpoints are useful for stalling the task execution long enough to watch the impact unfold. This is unusual, however, and should only be done if the stock breakpoints do not provide an adequate debugging facility.

General Guidelines

These guidelines are miscellaneous suggestions that make the task easier to use.

Use Connection Managers for Accessing Resources

Tasks should use connection managers for accessing any external resources such as files or connections. The benefits are numerous, including packages that are easier to understand and maintain. The taskUI should be written so that it is easy to create a new connection manager within the taskUI.

The TaskUI Should Be in a Separate Assembly

This is primarily for economy. For example, it is possible to eliminate all the taskUIs on server machines, making the packages safer and saving some drive space. However, there are also memory benefits. If the taskUI is in the same assembly, the taskUI can consume memory unnecessarily.