Creating a Custom SSIS 2012 Task – Coding the Task Editor

Posts in this series:

Adding an Editor Project

An SSIS task needs an editor. We begin creating the editor for MyFirstTask by adding a project to the MyFirstTask solution. Open the Visual Studio solution MyFirstTask.sln. In Solution Explorer right-click the solution, hover over Add, and click New Project as shown in Figure 1:

Figure 1: Adding a New Project

Select a Visual Basic Windows Class Library and name it MyFirstTaskUI:

Figure 2: Setting the Project Template

Rename Class1.vb to MyFirstTaskUI.vb:

Figure 3: Renaming MyFirstTaskUI

When prompted, click Yes to rename all Class1 references to MyFirstTaskUI references as shown in Figure 4:

Figure 4: Confirming the Rename

Adding References

As with the task project, we need to add references to this project. In Solution Explorer, right-click the MyFirstTaskUI and click Add Reference. When the Reference Manager displays, expand Assemblies and click Extensions. Scroll until you find Microsoft.SqlServer.Dts.Design and Microsoft.SqlServer.ManagedDTS, and select them as shown in Figure 5:

Figure 5: Adding References

TO use the new references, open the class named MyFirstTaskUI.vb. At the very top of this class, add the following Imports statements:

Imports System
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Design

MyFirstTaskUI.vb should now appear as shown in Figure 6:

Figure 6: Importing .Net Assemblies

Some Implementation

Next, we implement the IDtsTaskUI interface with the following statement added just after the MyFirstTaskUI class declaration:

Implements IDtsTaskUI

Your class will appear as shown:

Figure 7: Implementing IDtsTaskUI

The blue squiggly line beneath the interface name (IDtsTaskUI is a .Net interface) informs us of an issue. Hovering over the blue squiggly line causes a tooltip to display:

Figure 8: Interface Implementation Requirements

This message informs us we need to implement a Delete subroutine to make MyFirstTaskUI compliant to the IDtsTaskUI interface we are implementing. The Error List (View–>Error List) displays additional subroutines and one function required for implementing the IDtsTaskUI interface, shown in Figure 9:

Figure 9: Error List Showing Methods Required for IDtsTaskUI Implementation

We will need a TaskHost object variable to implement some of this functionality, so let’s add a new TaskHost variable just after the Implements statement:

Private taskHostValue As TaskHost

Figure 10: Adding a TaskHost Variable

Also, just because it’s a good idea to have one, let’s add a generic constructor (New method) for our class by adding the following code after the TaskHost variable declaration:

Public Sub New()
End Sub

Your class should now appear as shown in Figure 11:

Figure 11: Adding a Generic Constructor

Let’s implement the GetView method of the IDtsTaskUI interface by adding the following code:

Function GetView() As System.Windows.Forms.ContainerControl Implements IDtsTaskUI.GetView
Return New MyFirstTaskForm(Me.taskHostValue)
End Function

Your class should now appear as shown:

Figure 12: Implementing GetView

The blue squiggly beneath Me.taskHostValue is valid. It is warning us that there are no constructors (New methods) for the MyFirstTaskForm that accept arguments. We will address it in the near future

Next, let’s implement another New method – this one implementing the New method required by the IDtsTaskUI interface – by adding the following code:

Sub [New](ByVal parentWindow As IWin32Window) Implements IDtsTaskUI.New
End Sub

Your class should now appear as shown in Figure 13:

Figure 13: Implementing IDtsTaskUI.New

Let’s next add code to implement the Initialize method by adding the following to your class:

Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
Me.taskHostValue = taskHost
End Sub

Your class should now appear as shown here:

Figure 14: Implementing Initialize

Finally, let’s implement the Delete method by adding the following code:

Sub Delete(ByVal parentWindow As IWin32Window) Implements IDtsTaskUI.Delete
End Sub

Your class should now appear as shown in Figure 15:

Figure 15: Implementing IDtsTaskUI.Delete

Note the blue squiggly line beneath IDtsTaskUI in the Implements IDtsTaskUI statement disappears once we complete implementing the required interface methods. We have completed building the MyFirstTaskUI class. Save the class before proceeding.

Adding a Form

To add a form to our User Interface (Task Editor), right-click the MyFirstTaskUI, hover over Add, then click Windows Form:

Figure 16: Preparing to Add a Form

When the Add New Form window displays, name the form MyFirstTaskForm as shown in Figure 17:

Figure 17: Adding a New Form

Add a label and a textbox to the form. Change the text of the label to “Message:” and name the textbox “txtMessage” as shown in Figure 18:

Figure 18: Adding the Label and Textbox

Next, add a button named btnDone and set the Text property to “Done”:


Figure 19: Adding the Done Button

Finally, change the DialogResult property of btnDone to “OK” as shown in Figure 20:

Figure 20: Changing the DialogResult Property

We will use the button’s Click event to set the MyMessage property of the MyFirstTask. But setting the DialogResult property on the Ok or Done button is important. It accomplishes a couple automagic functions:

  • It closes the editor form.
  • Sending OK as the DialogResult triggers the Validate method of the Task.

Coding the Click Event and the Form

Double-click btnDone to open the editor at the Click event.

I deliberately choose to begin the discussion of coding the form in the Click event of btnDone. I can hear you thinking, “Why, Andy?” I’m glad you asked. The two bullets above represent a “gotcha” – an easy place to fail when coding a custom SSIS task editor. If you build this incorrectly, you will be hard pressed to find the answer by searching for “Validate method doesn’t fire” for custom SSIS task.

We’ll get to the actual code for the Click event in a bit. First, let’s build out the MyFirstTaskForm class.

Before we add code directly to the Click event of btnDone, add the following Imports statements to the very top of the Form class:

Imports System
Imports System.Globalization
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime

Your form code should now appear as shown in Figure 21:

Figure 21: Adding the Imports Statements to the MyFirstTaskForm Class

Next, add an Inherits Statement just after the MyFirstTaskForm class declaration:

Inherits Form

Your form class should now appear as shown:

Figure 22: Inheriting the Form Class

Declare a new TaskHost variable named TaskHostValue by adding the following code:

Private taskHostValue As TaskHost

Your form class should now appear as shown in Figure 23:

Figure 23: Declaring the TaskHost Variable

To begin coding the Click event, we need to address the task class via a class hosting the SSIS custom task class:

Me.taskHostValue.Properties(“MyMessage”).SetValue(Me.taskHostValue, CStr(Me.txtMessage.Text))

Your form class should now appear as shown:

Figure 24: Building the Click Event

In this subroutine – which fires when a data integration developer clicks the Done button on the MyFirstTask editor – the MyMessage property of the TaskHost that is coupled to this form is set to the text property value of the form textbox named txtMessage. We built a lot of code to get here, but this is why we did it; so we could edit a property from a task editor.

The last piece of editor coding is really the inverse of the previous operation. When the form displays we want to retrieve the value of the task object’s MyMessage property and display it in the txtMessage textbox on the editor (form). To accomplish this functionality, add the following constructor:

Public Sub New(ByVal taskHost As TaskHost)

Me.taskHostValue = taskHost
Me.txtMessage.Text = CStr(Me.taskHostValue.Properties(“MyMessage”).GetValue(Me.taskHostValue))

End Sub

When done, your form class should appear as shown here:

Figure 25: Adding the Form Constructor

This is a good stopping spot for this already-too-long post.

The form constructor takes a TaskHost argument named taskHost, and this satisfies the code in the MyFirstTaskUI class’s GetView function:

Figure 26: No More Blue Squiggly Line

You can obtain a copy of the code in the state at the end of this post here.

Next, we put together the task and editor!