Creating a Custom SSIS 2012 Task – Binding the Task and Editor

Posts in this series:

Let’s review. We began this series with a rambling introduction that disclosed some of how my brain works. I asked a question: “Do you think it is possible to create a custom SSIS 2012 task using Visual Basic 2012 Express?” Next we configured our development machine, Visual Studio Express 2012 for Windows Desktop, and a new project. We signed the project so that it would be accepted in the Global Assembly Cache (GAC) and continued by preparing the Visual Studio environment with all the accouterments and references necessary to build a custom SSIS task. We coded the task and its editor. And that brings us to here.

In this post we will couple the task and editor, add an icon, build and test the task.

Coupling the Task Editor to the Task

We now need to inform the task that is has an editor. Open the MyFirstTask solution and the MyFirstTask project in Visual Studio. Open the MyFirstTask class as shown in Figure 1:



Figure 1: MyFirstTask, as We Begin

The DTSTask attribute (decoration) is shown here and documented here. In its current state our task decoration has two values defined: DisplayName and Description. To couple the editor we add the multi-part attribute UITypeName by adding the following code:

UITypeName:=”MyFirstTaskUI.MyFirstTaskUI,” & _
“MyFirstTaskUI,Version=1.0.0.0,” & _
“Culture=Neutral,” & _
“PublicKeyToken=59546afe6ad4157d”

Once added to the DTSTask attribute decoration, it will appear similar to that shown in Figure 2:

AddingUITypeName
Figure 2: Adding UITypeName

You can find (some) documentation for the UITypeName attribute here. The property/value pairs are:

  • Type Name: MyFirstTask.MyFirstTaskUI
  • Assembly Name: MyFirstTaskUI
  • Version: 1.0.0.0
  • Culture: Neutral
  • Public Key: 59546afe6ad4157d

Where did the public key come from? Remember back in Creating a Custom SSIS 2012 Task – Signing the Assembly we created the key.snk file? We extracted the value and placed the public key in a comment. You can see the public key in Figure 2 on line 12.

We haven’t yet signed the Task Editor project. Let’s do that now. Double-click My Project under the MyFirstProjectUI project to open the project properties. Click on the Signing page, check the “Sign the assembly” checkbox, click the “Choose a strong key name file” dropdown, click Browse, browse to key.snk in the MyFirstTask project folder, and select that file as shown in Figure 3:



Figure 3: Signing the UI Project

Click on the Compile page and set the Build output path to <drive>:Program Files (x86)Microsoft SQL Server110DTSTasks where <drive> represents the installation drive for SQL Server 2012:



Figure 4: Setting the Build Path for MyFirstTaskUI

Save the My Project file and close it.

Earlier, we added a text file to MyFirstTask called MyFirstTaskNotes.txt. Open that file now and copy and paste the line beneath the “–register” heading. Edit the new line so MyFirstTask.dll now reads MyFirstTaskUI.dll as shown in Figure 5:



Figure 5: Adding a New Registration Command

Similarly, copy, paste, and edit the “–unregister” command as shown in Figure 6:



Figure 6: Adding a New Unregistration Command

Almost…

Our task is almost ready to build and test. What’s left? Look in the Execute method shown in Figure 7:



Figure 7: The Execute Method

Let’s add a message box command here to display the message contained in the MyMessage property by adding the following code:

MsgBox(_myMessage)

Your Execute method should appear similarly:



Figure 8: MsgBox Added

We are now ready for our first build!

Build It!

From the Build dropdown menu, click Build Solution:



Figure 9: Building the Solution

If all goes well, you should see verbiage similar to that shown in Figure 10:



Figure 10: Build Output

Next, copy the register commands from MyFirstTaskNotes.txt to the clipboard:



Figure 11: Copy the GACUtil Register Command 

Open the command prompt as an Administrator:



Figure 12: Opening the Command Prompt as Administrator

Paste the commands into the Administrator command prompt:



Figure 13: Pasting the Commands

If all goes as expected, you should see two assemblies successfully registered as shown in Figure 14:



Figure 14: Successfully Registered

Testing 1, 2, 3…

The moment of truth has arrived. Will the task work? Will it even show up in the SSIS Toolbox? Let’s open SQL Server Data Tools (SSDT) and find out! If all has gone according to plan, you will be able to open a test SSIS project and see the following on the SSIS Toolbox:



Figure 15: In the Toolbox!

Drag it onto the surface of  the Control Flow:



Figure 16: On the Control Flow!

Double-click the task to open the editor and add a test message:



Figure 17: Editing the Task

Click the Done button and execute the package in the debugger. Cue the sad trombone. Why? It was bound to happen sooner or later – the task failed. Look at the Progress / Execution Results tab to see what happened:



Figure 18: Validation Failure

The task failed validation because we have not supplied the contact information in the DTSTask attribute decoration.

I have to be honest here… I knew the task would fail if I left out one piece of information. I wanted it to fail so I could demonstrate how to unregister the task, clean the solution, fix the issue, rebuild the solution, re-register the task, and then test again.

If this sort of thing – failure – forces your blood pressure to rise, then stop. Stop right now. Don’t do any more. And I’m not just talking about this post, or even this series of posts; you’re going to want to consider some other type of software development at a minimum. Perhaps you want to consider a different career. In software development, failure is step in the right direction. If you cannot accept this fact on an emotional and intellectual level, you are in the wrong field.

First, delete the task from the Control Flow of the test SSIS project, save the test SSIS project, and close SSDT. Copy the unregister commands from the MyFirstTaskNotes.txt text file and paste them into the Administrator command prompt window:



Figure 19: Unregistering the Assemblies from the Global Assembly Cache (GAC)

Return to Visual Studio, click the Build dropdown menu and click Clean Solution:



Figure 20: Cleaning the Solution

If all goes according to plan, you will see the following output:

CleanSucceeded
Figure 21: Solution Cleaned

Fixing the Issue

One way to interpret the error returned from SSDT’s validation is: “You need to add the TaskContact attribute to the DTSTask decoration.” I happen to know that adding an icon will do the trick. So let’s do that.

Adding an Icon

I chose the Information_blue_6227.ico file from the Microsoft VS 2012 Modern Image Library. Begin by adding the icon file to the MyFirstTask project. In Solution Explorer, right-click the MyFirstTask project, hover over Add, and click Existing Item:



Figure 22: Adding an Existing Item

Navigate to the icon file and import it into the project:



Figure 23: Adding the Icon File

Once the icon file is added to the solution, you need to change the icon file build action properties. By default, the file’s Build Action property will be Content. You need to change it Embedded Resource as shown in Figure 24:



Figure 24: Changing the Icon File Build Action

This is important. If you do not change the Build Action property of the icon file from Content to Embedded Resource, the icon will not show up on your task.

Next, edit the DtsTask decoration to include the IconResource attribute. As shown in Figure 25, the attribute is the name of the task project and the name of the icon file:



Figure 25: IconResouce Attribute Added to DtsTask Decoration

Save the MyFirstTask class file and Build the solution following the directions above. Also following those directions, register the assemblies in the GAC. Open the test SSIS solution in SSDT and observe the SSIS Toolbox:


Figure 26: New Icon!

Add the task to the Control Flow and open the editor:



Figure 27: Configuring the Task

Click the Done button and start the SSDT debugger:



Figure 28: Viola!

And it works! Icon and all.

Congratulations!

:{>