P3.NET

Creating an SSIS Custom Task, Part 6

It is time to wrap up this series on creating a custom task for SSIS. We are going to finish our discussion with some advanced, but common, UI needs including:

  • Support for viewing and creating connections
  • Support for viewing and creating variables
  • Support for dynamic properties
  • Support for enumerating reports in SSRS

Type Converters

Before we get too much further it is important to ensure you are aware of type converters. Type converters are the magic that allow conversion to and from strings. There are many built in type converters. Basically whenever something like the property grid needs to convert to or from a string they try to get the type converter that is associated with the property/type. If they don’t find one then they will go to ToString. If you want control over the conversion process then a type converter is the way to go. One very useful converter is the StringConverter. This converter can be used to provide a pre-defined list of strings to the user. It is great for enumerations. But it does not necessarily have to limit the user from selecting from this list. For example our report format property for SSRS can technically be any value that is supported by SSRS or a report extension. There is a pre-defined list but we wouldn’t want to limit it to that. Therefore we’ll create a converter for the report format that includes the standard formats that SSRS supports but will allow the user to enter a different value if they want.

internal class ReportFormatTypeConverter : StringConverter
{
    public override StandardValuesCollection GetStandardValues ( ITypeDescriptorContext context )
    {
        var values = new[]
        {
            "PDF",
            "HTML5",
            "PPTX",
            "ATOM",
            "HTML4.0",
            "MHTML",
            "IMAGE",
            "EXCEL",
            "WORD",
            "CSV",
            "XML"
        };

        return new StandardValuesCollection(values.OrderBy(x => x).ToArray());
    }

    public override bool GetStandardValuesExclusive ( ITypeDescriptorContext context )
    {
        return false;
    }

    public override bool GetStandardValuesSupported ( ITypeDescriptorContext context )
    {
        return true;
    }
}

Because GetStandardValuesExclusive returns false, the user can type anything they want. But because GetStandardValuesSupported return true they will also get a dropdown list. To wire it up we need to go back to the GeneralViewNode and add a TypeConverter attribute to the ReportFormat property.

[TypeConverter(typeof(ReportFormatTypeConverter))]
public string ReportFormat 
{ ... }

Go ahead and build and load up the SSIS package and you should see this behavior working. We will rely on type converters for connections and variables.

Adding the SSRS Output View

Let’s go ahead and add a new Output view to the SSRS task. It’s only property is the variable where we’ll store the results of generating the report.

internal partial class OutputView : DtsTaskUIPropertyView<OutputViewNode>
{
    public OutputView ()
    {
        InitializeComponent();
    }

    protected override OutputViewNode CreateNode ( TaskHost host, IDtsConnectionService connectionService )
    {
        return new OutputViewNode(host);
    }

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

        task.Content = Node.Content;
    }
}
internal class OutputViewNode
{
    public OutputViewNode ( TaskHost host )
    {
        m_host = host;
            
        GenerateSsrsTask task;
        if (host.TryGetTask(out task))
        {
            Content = task.Content;
        };
    }

    [Category("Output")]
    [Description("Specifies the variable to store the report output into")]
    public string Content { get; set; }

    [Browsable(false)]
    public Variables Variables => m_host?.Variables;

    private readonly TaskHost m_host;
}

Finally, we need to update the GenerateSsrsTaskForm constructor to include the new view.

DTSTaskUIHost.AddView("Output", new OutputView(), null);

At this point we can set the Content property to a variable but it does not integrate nicely with the designer. Let’s fix that.

Supporting New Items

If you look at any of the existing SSIS tasks, each property that can be backed by a variable tends to show the list of variables and provide an option to create a new variable. This is implemented by combining a type converter with some custom code. Connections work similarly so it makes sense to generalize this as much as possible.

public abstract class NewItemStringConverter : StringConverter
{
    public abstract string NewItemText { get; }

    public bool IsNewItem ( string value )
    {
        return String.Compare(value, NewItemText, true) == 0;
    }

    public override TypeConverter.StandardValuesCollection GetStandardValues ( ITypeDescriptorContext context )
    {
        var values = new[] { NewItemText }.Union(GetValueList(context) ?? new string[0]);

        return new TypeConverter.StandardValuesCollection(values.ToArray());
    }

    public override bool GetStandardValuesExclusive ( ITypeDescriptorContext context )
    {
        return true;
    }

    public override bool GetStandardValuesSupported ( ITypeDescriptorContext context )
    {
        return true;
    }

    public abstract object ProcessNewItem ( INewItemContext context );

    protected abstract IEnumerable<string> GetValueList ( ITypeDescriptorContext context );
}

Since we will be reusing this converter we expose a property that specifies what the new item text will be. We also expose some functionality to let us know if an item is the “new item”. When getting the values in the list we prepend the new item to the list of values returned by the derived instance. Processing a new item depends upon the derived type. We will pass a context interface to the method so it has everything it needs to create the item.

The remaining piece is to actual handle the new variable selection. For that we need to hook into the property change notification, detect that this is a new item and call the ProcessNewItem method. Fortunately we already hooked the property change in our base DtsTaskUIPropertyView class so we need only implement it now.

protected virtual void OnPropertyChanged ( PropertyValueChangedEventArgs e )
{
    //Handle new item converters specially
    var converter = IsNewItem(e.ChangedItem);
    if (converter != null)
    {
        var target = GetTargetObject(e.ChangedItem);
        var property = e.ChangedItem.PropertyDescriptor;
        var oldValue = e.OldValue ?? "";

        try
        {
            this.Cursor = Cursors.WaitCursor;

            var context = new NewItemContext(this)
            {
                Target = target,
                TargetProperty = property
            };

            var newValue = converter.ProcessNewItem(context);
            if (newValue != null)
                property.SetValue(target, newValue);
            else
                property.SetValue(target, oldValue);
        } catch (Exception ex)
        {
            MessageBoxes.Error(this, "Error Creating New Item", ex);

            property.SetValue(target, oldValue);
        } finally
        {
            this.Cursor = Cursors.Default;
        };
    };
}

Supporting Variables

Now we can implement the variables converter.

public class VariablesTypeConverter : NewItemStringConverter
{
    public override string NewItemText
    {
        get { return "<New Variable>"; }
    }

    public override object ProcessNewItem ( INewItemContext context )
    {
        var defaultValues = GetNewVariableDefaults(context.TargetProperty, context.Target);
        var newVariable = context.Variables.PromptAndCreateVariable(context.ParentWindow, context.Container, defaultValues.Name, defaultValues.Scope, defaultValues.Type);

        if (newVariable != null)
            return newVariable.QualifiedName;

        return null;
    }

    protected override IEnumerable<string> GetValueList ( ITypeDescriptorContext context )
    {
        var provider = context.Instance.GetSpecializedObject<IDtsVariablesProvider>();
        if (provider != null)
        {
            return from v in provider.Variables.OfType<Variable>()
                    orderby v.QualifiedName
                    select v.QualifiedName;
        };

        return null;
    }

    private NewVariableAttribute GetNewVariableDefaults ( PropertyDescriptor property, object target )
    {
        var defaultAttr = property.GetAttribute<NewVariableAttribute>();

        if (defaultAttr != null)
            return defaultAttr;

        //Look for the interface
        var ifc = target as INewVariableProvider;
        if (ifc != null)
            return ifc.GetNewVariable(property.Name);

        return new NewVariableAttribute(property.Name, typeof(int));
    }
}

Let’s step through the behavior. Getting the list of existing variables requires that we get access to the variables. There is no way to do that from a converter nor does SSIS expose an interface for it. So we create a new interface called IDtsVariablesProvider that simply exposes the variables as a property. We’ll need to implement this on any node type that will need this functionality. Once we have the variables though we can easily enumerate through them to produce the list.

To create a new variable we need some basic information such as the default name, scope and type. This can vary by property so we create the NewVariableAttribute that can be used to specify this information. The attribute will be applied to any node property that is going to use the variable converter. When creating a new variable we will first look for this attribute. If we do not find it then we’ll look for the interface INewVariableProvider. This will allow a property to use more complex logic then simply setting a default value.

The ProcessNewItem method gets the new variable defaults as mentioned earlier, displays the New Variable window for SSIS and, if the user creates a new variable, returns the new variable name back so it can be assigned to the property.

Finally we can apply the converter. To do that we need to do a couple of things.

  • Apply the converter to any property that accepts variables
  • Add the NewVariableAttribute to the same property or implement INewVariableProvider if the defaults are more complex
  • Ensure the node implements the IDtsVariablesProvider interface so we can get access to the variable

For the OutputViewNode class we’ll update it accordingly.

internal class OutputViewNode : IDtsVariablesProvider
{
   ...

    [Category("Output")]
    [Description("Specifies the variable to store the report output into")]
    [TypeConverter(typeof(VariablesTypeConverter))]
    [NewVariable("ReportContent", typeof(object))]
    public string Content { get; set; }
}

Supporting Connections

This all the previous legwork in place supporting connections simply requires that we implement a new type converter. The only gotcha here is that there are different kinds of connections. We’ll create a base class for all connections but we’ll create a concrete version for HTTP since that is all we care about.

First the generic connection converter.

public class ConnectionsTypeConverter : NewItemStringConverter
{
    public ConnectionsTypeConverter ( string connectionType )
    {
        ConnectionType = connectionType;
    }

    public string ConnectionType { get; private set; }

    public override string NewItemText
    {
        get { return "<New Connection>"; }
    }

    protected override IEnumerable<string> GetValueList ( ITypeDescriptorContext context )
    {
        var provider = context.Instance.GetSpecializedObject<IDtsConnectionServiceProvider>();
        if (provider != null)
        {
            var connections = provider.ConnectionService.GetConnectionsOfType(ConnectionType);

            return from c in connections.OfType<ConnectionManager>()
                    orderby c.Name
                    select c.Name;
        };

        return null;
    }

    public override object ProcessNewItem ( INewItemContext context )
    {
        var connection = context.Connections.CreateConnection(ConnectionType);
        if (connection != null && connection.Count > 0)
        {
            var item = (ConnectionManager)connection[0];

            return item?.Name;
        };

        return null;
    }
}

As before we need to get the list of available connections but a converter does not have that and SSIS does not expose an easy way to get it so we create the IDtsConnectionServiceProvider interface to get it. Nodes will need to implement this interface.

To create a new connection we display the New Connection dialog from SSIS and, if the user creates a connection, return its name.

Here’s the HTTP specific converter.

public class HttpConnectionsTypeConverter : ConnectionsTypeConverter
{
    public HttpConnectionsTypeConverter () : base("HTTP")
    {
    }
}

Finally we can update the GeneralViewNode class to implement the interface and use the converter for the server connection.

internal class GeneralViewNode : IDtsConnectionServiceProvider
{
   ...

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

Querying for SSRS Reports

To make the designer more useful we would ideally like to query SSRS for the available reports. This is beyond the scope of SSIS and this series. Ultimately it involves making web service calls to SSRS to retrieve the available reports. You can read more about it here.

Since reports are a folder structure a treeview makes the most sense. To help with performance each node should only be expanded as needed. The attached code handles all this logic if you are interested. All we ultimately need to do is associate the ReportPath parameter in the GeneralViewNode with our custom UI type editor and it just works.

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

        m_path = value;
    }
}

Supporting SSRS Parameters

The last piece of the puzzle is to support the SSRS parameters. This is actually not as trivial as you might imagine. We can easily get the parameters from the code we wrote earlier. The issue is that ideally we want to expose these parameters in the property grid. For each parameter we want the user to be able to select either the value or variable to associate with it. To get all this to work is beyond the scope of this article and is provided in the code. Some discussion is useful though.

Each parameter itself is of type ParameterNode and looks a lot like the other nodes we have created. It exposes properties for the parameter name, the type (provided by SSRS) and whether the value is provided by a static value or variable. Supporting both is a common feature so we implement it using a cool trick of .NET. We previously added support for value vs variable to the runtime task so now we need to hook it up to the UI. Since this is generic functionality we’ll go ahead and rename ReportArgumentValueType to ValueOrVariable and move it into the core assembly.

Ultimately .NET relies on the ICustomTypeDescriptor interface to determine what members a type has. You can actually change the members exposed by a type simply by implementing this interface. For each parameter we’ll implement the interface. We’ll expose two properties: one for the value and one for the variable. Depending upon the ValueOrVariable for the parameter we’ll show one or the other property. This gives the user the illusion of swapping between properties.

[TypeConverter(ExpandableObjectConverter)]
internal class ParameterNode : ICustomTypeDescriptor, IDtsVariablesProvider, INewVariableProvider
{
  [Browsable(false)]
  public string Name { get; set; }

  [ReadOnly(true)]
  public Type Type { get; set; } 

    [TypeConverter(typeof(VariablesTypeConverter))]
    [RefreshProperties(RefreshProperties.Repaint)]
    public string Variable { get; set; }

    [RefreshProperties(RefreshProperties.Repaint)]
    public string Value { get; set; }

    [RefreshProperties(RefreshProperties.All)]
    [DefaultValue(ValueOrVariable.Variable)]
    public ValueOrVariable ValueType { get; set; }

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties ( Attribute[] attributes )
    {
        var properties = TypeDescriptor.GetProperties(this, attributes, true)?.OfType<PropertyDescriptor>();
        if (properties == null)
            return null;

        var actualProperties = new List<PropertyDescriptor>();
        foreach (var property in properties)
        {
            //Toggle Variable and Value properties based upon selected value type
            if (property.Name == "Variable")
            {
                if (ValueType == ValueOrVariable.Variable)
                    actualProperties.Add(property);
            } else if (property.Name == "Value")
            {
                if (ValueType == ValueOrVariable.Value)
                    actualProperties.Add(property);
            } else
                actualProperties.Add(property);
        };

        return new PropertyDescriptorCollection(actualProperties.ToArray());
    }
}

The Parameters view will use ParameterViewNode instead. This type is simply a collection of ParameterNode items. It also implements ICustomTypeDescriptor. For each report parameter it exposes a property with the same name and of type ParameterNode. This causes each parameter to appear as a separate property in the grid.

The ParameterView type is a standard property grid view as we’ve created before. But it also handles loading the parameters when it initializes. To do that it uses the SSRS web reference added earlier in combination with a background worker.

Now we can add the parameters to our form.

DTSTaskUIHost.AddView("Parameter Mappings", new ParameterView(), null);

Sharing Data Across Views

Sometimes it is necessary for views to share data. In this case we need to access one view from another. As an example, for SSRS the report arguments are determined by the report that is selected. Currently the view determines the report when it is initialized. If the user changes the report after initialization then the report arguments are wrong. Therefore we need to have the arguments view refresh its list whenever it is selected.

To help with this we will add a GetView method to the base view class. It will take a view type and return back the corresponding instance, if any.

protected T GetView<T> () where T : class, IDTSTaskUIView
{
    foreach (TreeNode node in ViewNode.TreeView.Nodes)
    {
        var view = ViewHost.GetView(node) as T;
        if (view != null)
            return view;
    };

    return null;
}

Now we need to update the arguments whenever the view gets selection by overriding the OnSelectionCore method inside ParameterView. Here’s the relevant code.

protected override void OnSelectionCore ()
{
    base.OnSelectionCore();

    var view = GetView<GeneralView>();

    //If there is no report defined yet then error
    if (String.IsNullOrWhiteSpace(view?.Node?.ReportPath) || String.IsNullOrWhiteSpace(view?.Node?.HttpConnection))
    {
        MessageBoxes.Error(this, "No Report Selected", "You must select a report before mapping the parameters.");
        PropertyGrid.Enabled = false;
        return;
    };

    //If the report has changed since the last time we loaded then refresh the arguments
    if (String.Compare(view.Node.ReportPath, m_currentReportPath, true) != 0)
    {
        RefreshArguments();
        m_currentReportPath = view.Node.ReportPath;
    };
}

Whenever the view is selected we get the GeneralView. If no report has been selected yet then this is an error case. Otherwise we compare the report to what we thought it was the last time we looked. If the report has been changed then we query for new report arguments and throw away whatever we had stored previously.

Final Thoughts

This has been another long journey but it has been instructive. We now have a working custom task to generate SSRS reports with a design time experience that is nice. Based upon this work we can expand to other things. For example at my company we are using custom tasks to query internal services directly rather than combining script tasks with HTTP connections. The options are limitless.

Download the code.

Comments