Custom SSIS Control Flow Component

• Download source - 13.2 KB

Introduction

This article is targeted towards the developer with experience in web or desk applications and who is beginning to develop custom components. A recent search for a tutorial on creating a custom control for SQLServer 2005 Integration Service (SSIS) revealed a shallow pool of available topics. The best examples I could find were at Microsoft's download center here. However, the examples had little more explanation than the short code comments and failed to go over some of the pitfalls that an inexperienced control developer (me) might find along the way. This article is written with the intent to go over a few of those pitfalls -- see points of interest below -- and explain the solution implemented.

Background

A recent web application project had a relatively large batch processing requirement each month. Roughly 30+ DTSX packages are run to load and process data prior to allowing user access to the web application. During the running of the processes, it is necessary to deny access to the web UI. This was accomplished through two tables in the database, JobsHeader and JobDetails, and a stored procedure to edit as necessary. With the need to manage the repetitive entries in the packages, it was obvious that a custom control would be the ticket.

SQLJobTask

As most good programmers will, I searched for an example to use, rather than reinvent the wheel. I found a good example in the SSIS programming samples: SQLConnectionManager, one of the examples in the previously mentioned download. There are two parts to a custom control:

• User interface
• Task

User interface

With the method chosen for controlling the web site access, there were really only two values needed: programId, and was it starting or stopping. The other information was used in other areas, but fit well here. When building the UI, there are two classes: the form itself and the UI logic.

Form

Creating a form follows the standard flow. The only new piece here is the use of the TaskHost object.

#region Private members
    private TaskHost _taskHost;
#endregion

TaskHost is the container for the task you are developing. This is how the user input for the task is passed between the form and the task. The form can populate TaskHost.Properties...

public SQLJobTaskForm(TaskHost taskHost)
{
    InitializeComponent();

    this._taskHost = taskHost;

    // Initialize TextBox controls with the values from the Task. 
    // These properties are accessed through the task's TaskHost object.
    this.txtProgId.Text = 
        this._taskHost.Properties[Constants.PROG_ID].GetValue(
        taskHost).ToString().Trim();
    .
    .
    .
}

...or it can use TaskHost.Properties to initialize the controls in the form.

private void btnOK_Click(object sender, EventArgs e)
{
    // Set the properties of the task through the TaskHost object.
    this._taskHost.Properties[Constants.ENABLE_SW].SetValue(
        this._taskHost, this.chkEnabled.Checked);
    .
    .
    .
}

UI Logic

The UI logic is relatively short. It has the responsibility of relaying the TaskHost object from the task to the form. This is done in two methods in the IDtsTaskUI interface:

namespace DMOE.DTS
{
    public sealed class SQLJobTaskUI : IDtsTaskUI
    {
        private TaskHost _taskHostValue;
        
        public void Initialize(TaskHost taskHost, 
            IServiceProvider serviceProvider)
        {
            // Store the TaskHost of the task.  
            // This will need to be passed to the form.
            this._taskHostValue = taskHost;
        }

        public System.Windows.Forms.ContainerControl GetView()
        {
            return new SQLJobTaskForm(this._taskHostValue);
        }
    .
    .
    .
    }
}

There are other methods that are in the IDtsTAskUI interface. However, they are not needed in this control. As is seen here, the SQLJobTaskUI is initialized with two parameters. These are TaskHost, the container we already mentioned, and IServiceProvider. The GetView() method passes the TaskHost object on to the instantiation of a new form object.

Task

This control inherits from the SQLTask object in the Control Flow objects category in SSIS and performs the bulk of the work. Surprisingly, this was where the complexity dropped.

namespace DMOE.DTS
{
    [
    Guid("FF12F4D7-7987-43cf-A5C8-2C31713C2B01"),
    DtsTask(DisplayName="SQLJobTask",
        TaskType="ExecuteSQLTaskDMOEJobs",
        IconResource = "DMOE.DTS.SQLJobTask.SQLJobTask.ico",
        UITypeName = "DMOE.DTS.SQLJobTaskUI,DMOE.DTS.SQLJobTask," +
            "Version=1.0.0.0,Culture=Neutral,PublicKeyToken=06021987c5ebca3a"
        )
    ]
    public sealed class SQLJobTask : Task, IDTSComponentPersist
    {
        #region .Ctor
        public SQLJobTask()
        {
        }
        #endregion

        #region Properties
            #region ProgramId
                private string _progID = string.Empty;
                public string ProgramId
                {
                    set { this._progID = value.Trim(); }
                    get { return this._progID; }
                }
            #endregion
        .
        .
        .
    }
}

As seen, the values from the UI form are going to be stored in fields. Very little coding is needed here. The TaskHost.Properties are set in by the form logic. You just have to remember that the property names are case sensitive and are accessed through TaskHost.Properties[index]. This index can be a string, index or identity. I chose to use a string and held the values in Constanst.cs. The call to the stored procedure is made in the overridden Execute() method that occurs at runtime.

Points of interest

First, create your own strong name file. You will need this for entry into GAC and to replace the public key token in the source code with this new public key token. There are numerous articles on the web that review the how-tos. See the references below on deploying. The UI is referenced by the DtsTask metadata in the task class.

DtsTask(DisplayName="SQLJobTask",
    TaskType="ExecuteSQLTaskDMOEJobs",
    IconResource = "DMOE.DTS.SQLJobTask.SQLJobTask.ico",
    UITypeName = "DMOE.DTS.SQLJobTaskUI,DMOE.DTS.SQLJobTask," +
        "Version=1.0.0.0,Culture=Neutral,PublicKeyToken=06021987c5ebca3a"

UITypeName is what tells the task what UI to call. Notice that the UI assembly and the task assembly are both listed, as well as the public key token that is retrieved from the strong name file. Take note of IconResource, which supplies the icon file name. I did not want the default icon assigned by the system.

At this point, the component was deployable and working from within the SSIS IDE. After deploying and running the component from the Integration Services SQL Server Management Studio, the component would not work. programId, the only required information, was not being saved from design time. It took a moment to realize that I had not persisted the properties. Once I realized this, the answer was fairly simple. The package has knowledge of the XML document and IDTSInfoEvents.

public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
    //    This might occur if the task's XML has been modified outside of 
    //    the Business Intelligence
    //    Or SQL Server Workbenches.
    if (node.Name != Constants.TASK_NAME)
    {
        throw new Exception(string.Format(
            "Unexpected task element when loading task - {0}.", 
            Constants.TASK_NAME));
    }
    else
    {
        // let error bubble up
        // populate the private property variables with values from 
        // the DTS node.
        this._progID = node.Attributes.GetNamedItem(Constants.PROG_ID).Value;
        .
        .
        .
    }
}

public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
    //create node in the package xml document
    XmlElement taskElement = 
        doc.CreateElement(string.Empty, Constants.TASK_NAME, string.Empty);
    
    // create attributes in the node that represent the 
    // custom properties and add each to the element
    //    ProgramId property
    XmlAttribute xaProgID = doc.CreateAttribute(string.Empty, 
        Constants.PROG_ID, string.Empty);
    xaProgID.Value = this._progID;
    taskElement.Attributes.Append(xaProgID);
    .
    .
    .
    //add the new element to the package document
    doc.AppendChild(taskElement);
}

The amount of information that needs to be covered -- such as deploying and debugging custom components -- can be daunting and is beyond the scope of this article. For more information, please see the references below.

References

• Building, Deploying and Debugging Custom Components: Excellent article from Microsoft.
• Professional SQL Server 2005 Integration Services by Brian Knight, et al: Another excellent resource from Wrox. Chapter 14 goes over the creation and deployment of a different type custom component. The information on the build and deployment is of particular importance.

History

• 25 May, 2007 -- Initial release
• 22 June, 2007 -- Article edited and moved to the main CodeProject.com article base