Posts in this series:
- Creating a Custom SSIS 2012 Task – The Rambling Introduction
- Creating a Custom Task in SSIS 2012 – Getting Started
- Creating a Custom Task in SSIS 2012 – Configuring the Project
- Creating a Custom SSIS 2012 Task – Signing the Assembly
- Creating a Custom SSIS 2012 Task – Preparing the Environment
- Creating a Custom SSIS 2012 Task – Coding the Task
- Creating a Custom SSIS 2012 Task – Coding the Task Editor
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:
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=220.127.116.11,” & _
“Culture=Neutral,” & _
Once added to the DTSTask attribute decoration, it will appear similar to that shown in Figure 2:
You can find (some) documentation for the UITypeName attribute here. The property/value pairs are:
- Type Name: MyFirstTask.MyFirstTaskUI
- Assembly Name: MyFirstTaskUI
- Version: 18.104.22.168
- 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:
Click on the Compile page and set the Build output path to <drive>:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\ where <drive> represents the installation drive for SQL Server 2012:
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:
Similarly, copy, paste, and edit the “–unregister” command as shown in Figure 6:
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:
Your Execute method should appear similarly:
We are now ready for our first build!
From the Build dropdown menu, click Build Solution:
If all goes well, you should see verbiage similar to that shown in Figure 10:
Next, copy the register commands from MyFirstTaskNotes.txt to the clipboard:
Open the command prompt as an Administrator:
Paste the commands into the Administrator command prompt:
If all goes as expected, you should see two assemblies successfully registered as shown in Figure 14:
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:
Drag it onto the surface of the Control Flow:
Double-click the task to open the editor and add a test message:
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:
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:
Return to Visual Studio, click the Build dropdown menu and click Clean Solution:
If all goes according to plan, you will see the following output:
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:
Navigate to the icon file and import it into the project:
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:
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:
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:
Add the task to the Control Flow and open the editor:
Click the Done button and start the SSDT debugger:
And it works! Icon and all.