Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Design-Time Properties for Custom SSIS Objects – Part 1 – Intro & Tasks

Matthew Gajdosik

Managing the editable properties of your custom objects in SQL Server Integration Services (SSIS) is a great way to improve their usability, but how can you? By properties, I mean the common fields exposed on the Properties tool window in Visual Studio, any time you’re working with a visual designer, and which you’ve probably used any time you wanted to make a precise change, or a quick change.

In SSIS, you can create your own class to handle all of the following types:

For the most part, these custom classes can be directly modified when editing SSIS packages in Visual Studio, and so anything properties you expose on the class can be edited right from Visual Studio without opening up any new UIs. For simple objects especially, these can help provide customization without needing to write up a complicated UI, which can save a lot of time when getting started. Best of all, since you wrote the code, you can control exactly when and how they can be used!

In this post, I’ll be focusing on Tasks, the building blocks of any Control Flow. Most of the listed types will have similar behaviors, but I’ll be reserving an extra post for some advanced approaches and for dealing with Data Flow / Pipeline Components, since they have their own metadata rules.

Tasks

Tasks are a great place to start, since the class you define in your custom code is directly the object you’re interacting with in the designer. Any public properties you expose on that class will be visible by default, and so there’s no extra work involved.

Let’s take a basic sample task from an earlier post, and add the string property SampleString.

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task
{
    public string SampleString { get; set; }

    /* Class Body Here */
}

With just this property added, if I build my library and place in the appropriate folders (check the earlier post link to find out how to quickly create your own Windows installer using WiX), I immediately can find my new property:
task_basic_custom_properties

Using Property Attributes

If you’re not familiar with attributes, they’re basically a way of “tagging” different items in your code to allow other systems to recognize them and treat them in a certain way. And for Properties, there are a bunch of predefined attributes that you can use to control how the Properties tool window in Visual Studio will use them.

The sample code below shows a few that may help:

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task
{
    [Description("This is a test property.")] // Set the description of the property when selected
    [Category("Test Category")] // Set the property category on the Properties tool window
    [DisplayName("Sample String!")] // Control how the field is named in the tool window

    [MergableProperty(true)] // Allows the property to be edited in multiple places at the same time, if multiple objects that define it are selected
    [ReadOnly(true)] // This doesn't work as-is! Check the next section if you need this.
    public string SampleString { get; set; }

    [Description("This is also a test property.")] // Set the description of the property when selected
    [Category("Test Category")] // Set the property category on the Properties tool window
    [DisplayName("Another Sample String")] // Control how the field is named in the tool window

    [MergableProperty(false)] // Setting this to false (the default) causes the property to disappear if more than one object is selected
    [PasswordPropertyText(true)] // Setting this to true hides the field value like a password field (off/false by default)
    public string AnotherSampleString { get; set; }

    /* Class Body Here */
}

Taking a look at these in Visual Studio, we can pretty quickly see the impact:

task_organized_properties

Each property is organized into the category we specified (“Test Category”), uses the overridden display names (“Another Sample String”, “Sample String!”), and has a description we’ve set. This can be extremely useful for users, since it helps provide context and some hints as to what your property corresponds to.

task_merged_properties

With more than one task selected (the rounded bullets on the corners of the task are the main indicator), you can see which general properties can be merged, including the Sample String! property we defined. Editing them from this interface will set the value on both tasks.

task_password_example

And last but not least, you can see the impact of PasswordProperty(true), which masks the value in Another Sample String. Of course, the ReadOnly attribute we set did nothing as well, but how else can we implement that, then?

Complex Task Data

Before moving on, there’s one major caveat to everything I’ve mentioned so far: every single value you use needs to be XML-serializable by default. There are some more specific rules than that, but since everything is ultimately stored in an XML-based .dtsx file, it’s a good rule of thumb. However, Microsoft thought to cover this, and so you can add code to handle complex object persistence by implementing the IDTSComponentPersist interface, and its corresponding load and save methods.

This will come in handy if you ever want to do the following:

  1. Store a non-serializable class.
  2. Gracefully handle errors, special rules, and version changes in your object metadata.

Now, since you may not be storing all of your class metadata in the root object, and since you likely are coming up with more complex rules for editing this metadata, you can look at further customizing your properties.

Since they’re driven by getters and setters, one simple trick you can add is to set a property to read-only just by removing the setter. You can also try defining the get method, rather than leaving it to be implemented implicitly. This could be useful if you want to display information to the user or developer, such as the name of an assigned Connection Manager.

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task
{
    // Now the property is read-only
    public string SampleString { get; }
    [Browsable(false)] // And the other property is hidden
    public string AnotherSampleString { get; set; }

    /* Class Body Here */
}

And if you store data in a child class, you can also expose it to the editor in the same way:

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task, IDTSComponentPersist
{
    private MyActualMetadataClass _myMetadata { get; set; }
    public string SampleString
    {
        get { return _myMetadata.SampleString; }
        set { myMetadata.SampleString = value; }
    }

    /* Class Body Here */
}

Using Variables and SSIS References

Of course, in the designer you can also access some of the SSIS internals. If you don’t mind taking the time, the following additions are interesting to say the least, in part because they actually work… to an extent.

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task
{
    // Both classes are found under Microsoft.SqlServer.Dts.Runtime as well
    public ConnectionManager SampleConnectionManager { get; set; }
    public Variable SampleVariable { get; set; }

    /* Class Body Here */
}

 

task_editable_variables

Now, there are two issues here:

  1. Persistence of the objects isn’t possible as-is, and reloading the package will lose these fields and return a number of errors. These are runtime objects, and runtime objects in SSIS are almost always .COM components and therefore a challenge to serialize even in the best cases.
  2. The interface is not particularly helpful, as each item is represented by a (valid and appropriate, but hard to reference) GUID.

So how can you load these different object types? Well, the simplest way would be to maintain those values as strings, and use the getters and setters to force the inputs to be valid by picking up the local Connections and Variables, but that’s not very user friendly.

Since we know that these objects are accessible at design-time, and even can be accessed in a (relatively) friendly way from the designer interface, we can extend this interface and define a fully-fledged custom property editor for the appropriate object type and any limitations you may want to impose.

Unfortunately, that’s a big beyond the scope of this post, but the next post in this series will cover some strategies here, as well as using TypeConverters to handle the same situation for components in the Data Flow stage.

Results and Next Steps

So now you should be able to take advantage of properties to accelerate your own custom object development without needing to invest in a full UI right away, even with complex objects. By controlling the getters, setters, and common property attributes, you can fully customize how the users/developers can use your objects, including accessing some other native SSIS objects.

The next post in this (short) series will focus on how to achieve similar functionality when writing custom PipelineComponents (the building blocks of a data flow), as they have their own unique way of being stored and call. It will also help give some advanced tips, like how you can handle complex SSIS objects like Variables. For now, if you have any questions on the current post, or requests for future posts, please feel free to comment below!

_________________________________________________________________________________________

To learn more about Tallan’s Data & Analytics solutions, and how they can transform your organization’s data into business intelligence and action, CLICK HERE.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>