Creating a Custom SSIS 2012 Task – Coding the Task

Posts in this series:

We’ve worked through several posts in preparation for this post, but we are finally ready to begin coding the custom task in earnest.

Using a Reference

Open the Visual Studio solution MyFirstTask. In Solution Explorer open the class MyFirstTask.vb. At the very top of the class, add the line:

Imports Microsoft.SqlServer.Dts.Runtime

Your class now appears as shown in Figure 1:

Figure 1: Using a Reference

This line of code allows you to use objects, methods, and properties contained in the assembly Microsoft.SqlServer.ManagedDTS – we added a reference to this assembly in Creating a Custom Task in SSIS 2012 – Configuring the Project.

Decorating the Class

If you are reading this when it was published, I know what you’re thinking: “Andy, the holidays are almost over. Why are we decorating now?” It’s not that kind of decorating. 🙂

We decorate to inform Visual Studio this class is “different.”  In this case, the class is different because it is part of an SSIS task. Our decoration code goes just prior to the class definition, and is composed of these lines:

<DtsTask(DisplayName:=”MyFirstTask”, _
Description:=”My first task.”)> _

Figure 2: Decorating the Class

Visual Studio now knows more about what we’re building.

Inheriting from Task

Beneath the class definition place the following line of code:

Inherits Task

Your class should appear as shown in Figure 3:

Figure 3: Inheriting Task

The actions taken so far combine to create a relationship between our code and the base code included in the ManagedDTS assembly we referenced earlier. Now it’s time to build on that relationship by adding the things we want our task to do.

Adding a Property

In a class, a property is coded using an internal private variable and publicly accessible property. Inside the class, we will use the private variable. The property provides a mechanism to expose the value of the internal variable to objects outside the class.

Create the internal variable by adding this line of code after the Inherits line:

Private _myMessage As string

Your class should now appear as shown in Figure 4:

Figure 4: Adding a Private Variable

The variable _myMessage will hold a string value, and we will use this internally in our task. We will expose the value of _myMessage externally via a property named MyMessage. Let’s construct that property now by adding the following line of code to the MyFirstTask class:

Public Property MyMessage As String

Your class should now appear as shown in Figure 5:

Figure 5: Adding a Property, Part 1

Properties can be read-only, write-only, and subject to many other conditions. Most properties are read-write, like the MyMessage property we are constructing. To continue coding the property, type “Get” and press Enter on the line beneath the Property line. The remaining code for the property is added automatically as shown in Figure 6:

Figure 6: Adding a Property, Part 2

Finally, we couple the value of _myMessage to the property by returning the value of the variable on read (Get) and writing the value passed to the property on write (Set). The read code looks like this:

Return _myMessage

The write code looks like this:

_myMessage = value

When the read and write code is added to the Property, it is complete:

Figure 7: Completing the Property

Task Methods

Earlier we added an Inherits statement to the MyFirstTask class – Inherits Task, remember? When you inherit one class in another, the inherited class is identified as the Base Class. In Visual Basic, it can be addressed using the keyword MyBase. You can observe a bunch of information about a base class by right-clicking the base class in the Inherits statement and then clicking “Go To Definition” as shown in Figure 8:

Figure 8: Opening Object Browser

Clicking Go To Definition open Object Browser to the base class, as shown in Figure 9:

Figure 9: Viewing the Task Class in Object Browser

Methods contained in this class are listed on the right. If we select them we get details below as shown in Figure 10:

Figure 10: Details of the InitializeTask Method

Notice the InitializeTask subroutine is marked as “Overridable”. When we inherit the Task class, we can override the InitializeTask method in our inheriting class. We will override this method  and two others: Validate and Execute. To override the InitializeTask method add the following code:

Public Overrides Sub InitializeTask(ByVal connections As Connections, _
ByVal variableDispenser As VariableDispenser, _
ByVal events As IDTSInfoEvents, _
ByVal log As IDTSLogging, _
ByVal eventInfos As EventInfos, _
ByVal logEntryInfos As LogEntryInfos, _
ByVal refTracker As ObjectReferenceTracker)

End Sub

When you add this code, your code should appear as shown:

Figure 11: Overriding InitializeTask

Not surprisingly, the InitializeTask method executes when the task is added to an SSIS Control Flow.

Next, let’s override the Validate method by adding the following function to our class:

Public Overrides Function Validate(ByVal connections As Connections, _
ByVal variableDispenser As VariableDispenser, _
ByVal componentEvents As IDTSComponentEvents, _
ByVal log As IDTSLogging) As DTSExecResult

Return DTSExecResult.Success

End Function

After adding this code, your class should appear similar to that shown in Figure 12:

Figure 12: Overriding the Validate Function

The Validate method is called when the task is added to the SSIS Control Flow, when a property changes, and when a task is executed. The line Return DTSExecResult.Success tells the method to return Success – which translates to “I think I am ready to run” in TaskSpeak – to the Control Flow when the method is invoked.

Next add the Execute method override by adding the following code to the MyFirstTask class:

Public Overrides Function Execute(ByVal connections As Connections, _
ByVal variableDispenser As VariableDispenser, _
ByVal componentEvents As IDTSComponentEvents, _
ByVal log As IDTSLogging, _
ByVal transaction As Object) As DTSExecResult

Return DTSExecResult.Success

End Function

Your class should resemble that shown here:

Figure 13: Overriding Execute

That is all we are going to code in this task. Trust me, there are a lot of other things you want to consider if you’re building a commercially viable product. But this series, as involved as it is, is merely an introduction.

You can download a copy of the project in this state here.

Next up: Building a user interface for the task.