Creating a Custom SSIS 2012 Task – Notes From My Experience

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
• Creating a Custom SSIS 2012 Task – Binding the Task and Editor

While learning to build custom SSIS 2012 tasks, I encountered elementary errors that I believe experienced .Net developers avoid. When I searched for solutions, I found very little help. The reason? I believe the issues I encountered are not typical for developers building solutions targeted at the Global Assembly Cache (GAC). When one begins developing in the Controls space, one is expected to Just Know certain things about .Net development.

I did not know some of those things. What did I encounter?

1. Start Visual Studio as an Administrator so you can Build to a System Folder:

When you open Visual Studio, right-click the VS Express for Desktop tile and then click the “Run as administrator” button at the bottom of the screen:

Figure 1: Run VS Express for Desktop as Administrator

When you click Run VS Express for Desktop as Administrator, you will be prompted to confirm you wish to run Visual Studio Express 2012 for Windows Desktop:

Figure 2: Confirm you Really Want to Run VS Express for Desktop as Administrator

Why?

This saves the additional step of copying the DLL assemblies from the bin\Debug folder to the …\DTS\Tasks folder. You need the DLLs in the Tasks folder so SQL Server Data Tools can locate (and load) them for SSIS 2012 development. You need the DLLs in the GAC so SSIS can utilize them at run-time.

2. Learn How to Recover

Backups are useless. Recoveries are priceless. Start with a recovery strategy. Ask yourself, “Self, if something tragic happens, how will I get back to Square One?” Answer that question early. You will be glad you did.

I could not find a place to enter Post-Build events in Visual Studio 2012 Express for Windows Desktop when building Visual Basic applications. I find this functionality for Visual C# and I am disappointed it is not present for VB developers.

Building a Notes file helped immensely. My Notes file holds commands to unregister and register my DLLs in the GAC, along with key-generation commands for building public/private key pairs using the Strong Name utility. As of the end of this project, my Notes file appears as shown here:

– key generation
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe” -k key.snk
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe” -p key.snk public.out
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe” -t public.out

– register
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe” -if “E:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\MyFirstTask.dll”
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe” -if “E:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\MyFirstTaskUI.dll”

– unregister
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe” -u MyFirstTask
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe” -u MyFirstTaskUI
Listing 1: Contents of MyFirstTaskNotes.txt

Cleaning the solution is the other step in my four-step recovery process outlined in the following synopsis. When something goes wrong:

1. Unregister the assemblies from the GAC
2. Clean the solution
3. Correct the issue in the code and Build the solution
4. Register the assemblies in the GAC

Open the command prompt as an Administrator:

Figure 3: Opening the Command Prompt as Administrator

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

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

Figure 5: Cleaning the Solution

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

Figure 6: Solution Cleaned

Code until you are ready for the next test.

From the Build dropdown menu, click Build Solution:

Figure 7: Building the Solution

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

Figure 8: Build Output

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

Figure 9: Copy the GACUtil Register Command 

Paste the commands into the Administrator command prompt:

Figure 10: Pasting the Commands

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

Figure 11: Successfully Registered

3. Change the Icon File’s Build Action Property to Embedded Resource

This particular issue stumped me for ____ (I am embarrassed to say how long, but it was too long). Here’s how I got a custom SSIS task icon to display in the SSIS 2012 Toolbox:

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 12:

Figure 12: 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.

Conclusion

Kudos to Microsoft for providing developers with the ability to build custom SSIS 2012 tasks in Visual Studio Express! I hope you have enjoyed reading this material as much as I enjoyed creating it.

As always, I welcome your feedback, suggestions, and comments.

:{>