In the previous post we finished the runtime side of the SSIS task. It’s time to work on the design side. The design consists of a standard Winforms form and controls. If you already know Winforms then you are halfway there. If not then you should read up on it first.

Setting Up the Projects

The design time components are not needed at runtime and therefore should reside in their own assembly. Following the pattern set up for existing tasks we’ll create a new project following the same name as the runtime project but with a .UI on the end. As we did for the runtime components, we’ll create a base UI project where the design framework will live. We’ll refer to this as the framework UI project. We’ll then create a separate project for each custom task. We’ll refer to this as the task UI project.

Both projects following the same guidelines as already used for the runtime with the addition of some extra references.

• Project Type = Class Library
• Framework Version = .NET 4.5.2
• Sign the assembly using the SNK file in the solution
• Update the AssemblyVersion.cs file
• The same build events (pre and post) are also needed EXCEPT the design time assemblies do not need to be copied to any specific folder as they are retrieved from the GAC
• Add the following assembly references
• P3.IntegrationServices (Project)
• Runtime assembly containing the custom task (Project, for the task’s UI project only)
• Microsoft.SqlServer.Dts.Design 12.0 (Framework\Extensions)
• Microsoft.SqlServer.ManagedDTS 12.0 (Framework\Extensions)
• System.Drawing (Framework)
• System.Runtime.Serialization (Framework)
• System.Windows.Forms (Framework)
• Microsoft.DataTransformationServices.Controls (Browse)

The last assembly is stored in the older version of the GAC and therefore will not show up by default. It can be found at %Windows% \ Microsoft.Net \ assembly \ GAC_MSIL \ Microsoft.DataTransformationServices.Controls \ v4.0_12.0.

Go ahead and do a test build to ensure everything compiles and registers properly for both projects.

Task UI

In SSIS each task has a UI which is implemented as IDtsTaskUI. This interface is really just needed to initialize the UI and get the actual form. From a framework point of view only the form itself is really custom so we’ll create a base class in the framework UI project that implements the interface and provides virtual methods to override. Only creating the form is required.

public abstract class DtsTaskUI : IDtsTaskUI
{
    public void Delete ( IWin32Window parent )
    {
        DeleteCore(parent);
    }

    public ContainerControl GetView ()
    {
        return GetViewCore();
    }

    public void Initialize ( TaskHost taskHost, IServiceProvider serviceProvider )
    {
        Host = taskHost;
        ServiceProvider = serviceProvider;

        InitializeCore();
    }

    public void New ( IWin32Window parent )
    {
        NewCore(parent);
    }

    protected TaskHost Host { get; private set; }

    protected IServiceProvider ServiceProvider { get; private set; }

    protected virtual void DeleteCore ( IWin32Window parent )
    { }

    protected abstract ContainerControl GetViewCore ();

    protected virtual void InitializeCore ()
    {
    }

    protected virtual void NewCore ( IWin32Window parent )
    { }
}

Each task will have its own implementation that derives from this base class.  The form that is returned is a standard Winforms form that derives from DTSBaseTaskUI. Here’s how the SSRS task implements this.

public class GenerateSsrsTaskUI : DtsTaskUI
{
    protected override ContainerControl GetViewCore ()
    {
        return new GenerateSsrsTaskForm(Host, ServiceProvider.GetService<IDtsConnectionService>());
    }
}

And here’s the form code.

public partial class GenerateSsrsTaskForm : DTSBaseTaskUI
{
    public GenerateSsrsTaskForm ( TaskHost taskHost, object connections ) : base(GenerateSsrsTask.TaskName, GenerateSsrsTask.Icon, GenerateSsrsTask.Description, taskHost, connections, true)
    {
        InitializeComponent();
    }
}

Notice that it is reusing the constants we defined on our runtime task previously. This makes it easier to keep things in sync. We could add more parameters if we want but this should be sufficient. Note that by doing this we have broken the designer. So don’t be surprised when you cannot open the form in the designer anymore.

The base form renders the content as “views” where each view is its own tab. Our form is nothing more than a container for the views that do all the work. Our form will determine what views to show, the order they appear and which one is the starting view. Notice the last parameter to the base constructor is true. This automatically adds support for the Expressions view which provides users with an alternative approach to setting the task properties. It does not require any extra effort so having it available is useful.

Task Views

Views expose a lot of functionality including loading, saving, validation and selection change notifications. The IDTSTaskUIView interface wraps all this functionality. Similar to the runtime task, we’ll wrap this in an abstract base class that is responsible for handling the common behavior, saving the important values passed in and basically interacting with the parent.

public abstract partial class DtsTaskUIView : UserControl, IDTSTaskUIView
    {
        public DtsTaskUIView ()
        {
            InitializeComponent();
        }

        public void OnCommit ( object taskHost )
        {
            Host = taskHost as TaskHost;
            if (Host == null)
                throw new ArgumentException("Host is invalid.", nameof(taskHost));

            if (m_initialized)
                Save();
        }

        public void OnInitialize ( IDTSTaskUIHost treeHost, TreeNode viewNode, object taskHost, object connections )
        {
            ViewHost = treeHost;
            ViewNode = viewNode;
            Host = taskHost as TaskHost;
            if (Host == null)
                throw new ArgumentException("Host is invalid.", nameof(taskHost));

            ConnectionService = connections as IDtsConnectionService;
            if (ConnectionService == null)
                throw new ArgumentException("Connection service is invalid.", nameof(connections));

            OnInitializeCore();
            m_initialized = true;
        }

        public void OnLoseSelection ( ref bool bCanLeaveView, ref string reason )
        {
            bCanLeaveView = OnLoseSelectionCore(ref reason);
        }

        public void OnSelection ()
        {
            OnSelectionCore();
        }

        public void OnValidate ( ref bool bViewIsValid, ref string reason )
        {
            bViewIsValid = !OnValidateCore(ref reason);
        }

        protected IDtsConnectionService ConnectionService { get; private set; }

        protected TaskHost Host { get; private set; }

        protected IDtsVariableService VariableService
        {
            get { return Host?.Site?.GetService(typeof(IDtsVariableService)) as IDtsVariableService; }
        }

        protected IDTSTaskUIHost ViewHost { get; private set; }

        protected TreeNode ViewNode { get; private set; }

        protected T GetTask<T> () where T : Task
        {
            return Host.GetTask<T>();
        }

        protected abstract void OnInitializeCore ();

        protected virtual bool OnLoseSelectionCore ( ref string reason )
        {
            return true;
        }

        protected virtual void OnSelectionCore ()
        {
        }

        protected virtual bool OnValidateCore ( ref string reason )
        {
            return true;
        }

        protected abstract void Save ();

        private bool m_initialized;
    }

Most of these methods are self explanatory.

• OnInitialize is called when the view is first shown. It may not be called at all if the user never selects it. Any first-time initialization should occur here including loading data from the runtime task.
• OnSelection is called each time the view is selected, including the first. It should update the UI with any changes that have been made while the form is shown.
• OnLostSelection is called when the view loses selection. If the view cannot lose selection then return true from the method.
• OnValidate is called to validate the view data before it is saved. Any errors will prevent the form from saving.
• OnCommit is called when the form is dismissed. It should be where the data is saved. Be aware that this method is called even if the view has never initialized. The base type handles this scenario before calling the core method.

Property Views

The biggest decision we need to make about a view is what type it is. In SSIS many of the views are simply property grids. This makes it very easy to create a UI with complex objects without the need for writing a bunch of code. It also makes the view consistent with other views. That is the approach we will take here.

The alternative view is a completely custom view. In this approach you will create a standard control that wraps the functionality you want.It requires more work but allows for more complex UIs. We will not do that for this article but the DtsTaskUIView is set up to support this type of view as well.

For a property-based view we will use the property grid control. The standard SSIS tasks follow a simple pattern that works well so we will do the same thing here. Each view is a generic type that specifies the data to show in the grid. The data is represented by a separate “node” type. The node type has the properties to be rendered, any annotations to control how they look and all the logic needed to initialize the node from the runtime task. To help create these we will create yet another base type specifically for property grids.

public abstract partial class DtsTaskUIPropertyView<T> : DtsTaskUIView where T : class
{
    public DtsTaskUIPropertyView ()
    {
        InitializeComponent();
    }

    public T Node => ((PropertyGrid?.LocalizableSelectedObject as DTSLocalizableTypeDescriptor)?.SelectedObject ?? PropertyGrid.LocalizableSelectedObject) as T;

    protected LocalizablePropertyGrid PropertyGrid => m_propertyGrid;

    protected internal abstract T CreateNode ( TaskHost host, IDtsConnectionService connectionService );

    protected override void OnInitializeCore ()
    {
        PropertyGrid.PropertyValueChanged += OnPropertyValueChanged;

        PropertyGrid.LocalizableSelectedObject = CreateNode(Host, ConnectionService);
    }

    protected virtual void OnPropertyChanged ( PropertyValueChangedEventArgs e )
    {
    }

    private void OnPropertyValueChanged ( object s, PropertyValueChangedEventArgs e )
    {
        OnPropertyChanged(e);
    }
}

This control simply sets up a localized property grid (provided by SSIS). It hooks into the property change notification (for later use). It requires the type of the data to display in the grid (the node). It also provides an abstract method to create the node when it is loaded (CreateNode). We will expand this functionality later.

Property View Nodes

A node is simply any type that exposes properties. We do not need any base class so we can use any type we want. We will want to ensure that we pass any parameters to the type that we’ll need when rendering and validating the node though. All this will be done through the CreateNode method of the view.

SSRS Task UI

Let’s go ahead and finish out this article by setting up the first view for the SSRS task. We’ll call it General and it’ll expose the core properties that the SSRS task needs to have set. We’ll add additional views later to handle the other properties.

First we add a new property view and define the node type containing the properties.

internal partial class GeneralView : DtsTaskUIPropertyView<GeneralViewNode>
{
    protected override GeneralViewNode CreateNode ( TaskHost host, IDtsConnectionService connectionService )
    {
        return new GeneralViewNode(host, connectionService);
    }

    protected override void Save ()
    {
        var task = GetTask<GenerateSsrsTask>();

        Host.Name = Node.Name;
        Host.Description = Node.Description;

        task.ServerConnection = Node.HttpConnection;
        task.ReportFormat = Node.ReportFormat;
        task.ReportPath = Node.ReportPath;
    }
}

The view simply creates an instance of the node type and, when saving, saves the results back to the task.

internal class GeneralViewNode
{
    public GeneralViewNode ( TaskHost host, IDtsConnectionService connectionService )
    {
        ConnectionService = connectionService;

        m_name = host.Name;
        m_description = host.Description;

        GenerateSsrsTask task;
        if (host.TryGetTask(out task))
        {
            HttpConnection = task.ServerConnection;
            m_format = task.ReportFormat;
            m_path = task.ReportPath;
        };
    }

    [Browsable(false)]
    public IDtsConnectionService ConnectionService { get; private set; }

    [Category("General")]
    [Description("Specifies the name of the task.")]
    public string Name
    {
        get { return m_name ?? ""; }
        set {
            if (String.IsNullOrWhiteSpace(value))
                throw new ArgumentException("Name cannot be empty.", "value");

            m_name = value.Trim();
        }
    }

    [Category("General")]
    [Description("Specifies the description of the task.")]
    public string Description
    {
        get { return m_description ?? ""; }
        set { m_description = value.Trim(); }
    }

    [Category("Connection")]
    [Description("Specifies the HTTP connection for the reporting server.")]
    public string HttpConnection
    {
        get { return m_connection ?? ""; }
        set { m_connection = value; }
    }

    [Category("Connection")]
    [Description("Specifies the format of the report.")]
    [DefaultValue("PDF")]
    public string ReportFormat
    {
        get { return m_format ?? ""; }
        set { m_format = value; }
    }

    [Category("Connection")]
    [Description("Specifies the path and name of the report.")]
    public string ReportPath
    {
        get { return m_path ?? ""; }
        set {
            if (value != null)
                value = value.EnsureStartsWith("/");

            m_path = value;
        }
    }

    private string m_name, m_description;

    private string m_connection;
    private string m_format = "PDF", m_path;   
}

The node consists of properties to back each of the runtime task values along with any desired annotations. We will discuss why we need the node parameters later.

The next thing we need to do is add the view to the form. We modify the form’s constructor to create the view and add it to the tree.

public GenerateSsrsTaskForm ( TaskHost taskHost, object connections ) : base(GenerateSsrsTask.TaskName, GenerateSsrsTask.Icon, GenerateSsrsTask.Description, taskHost, connections, true)
{
    InitializeComponent();

    var startView = new GeneralView();

    DTSTaskUIHost.FastLoad = false;
    DTSTaskUIHost.AddView("General", startView, null);
    DTSTaskUIHost.FastLoad = true;

    DTSTaskUIHost.SelectView(startView);
}

Wiring Up the UI to the Task

We have now created the base UI for the task but we have not yet hooked it up to the runtime component. Time to go back to the runtime task’s class and update the DtsTask attribute to use the new UI we created.

[DtsTask(DisplayName = GenerateSsrsTask.TaskName, RequiredProductLevel = DTSProductLevel.None, Description = GenerateSsrsTask.Description
        ,IconResource = "P3Net.IntegrationServices.Tasks.Ssrs.Images.GenerateSsrsTask.ico"
        ,UITypeName = "P3Net.IntegrationServices.Tasks.Ssrs.UI.GenerateSsrsTaskUI, P3Net.IntegrationServices.Tasks.Ssrs.UI, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=1a60bea6e1da755b")]

Unfortunately this uses the old-school approach to relating types by requiring the fully-qualified assembly and type name. That means that every time you update the version of the UI assembly you have to modify this attribute. Additionally the public key token must be obtained by building the assembly and then using a tool (like JustDecompile) to extract the public key out so you can store it. Notice that the type we specify is the IDtsTaskUI-derived type and not the form.

Once you have built the code you should be able to return to our SSIS demo package and double click the custom task. This time when you double click you will get your UI to show rather than an error about not having a designer. The UI is not finished yet by far. We don’t support all the properties of the task, we don’t have nice support for connections and variables nor do we have an ideal approach to getting the report information. We will resolve all that in the next, and hopefully last, article.

Download the code.