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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.