Creating a Custom SSIS 2012 Task – Preparing the Environment

Posts in this series:

One of the first things martial arts students are taught is how to fall. Why? So they don’t hurt themselves. As we begin developing this task, you are going to experience failures. Tests will fail, code will not behave as anticipated, things will go wrong. When they do, I want you to know a couple things:

  1. Failure is normal.
  2. How to recover.

Recovery

I cannot overemphasize the need for source control. I recommend Microsoft’s Team Foundation Service. At the time of this writing, TFS is free for teams of five or less. Source control is your number one method for code recovery. Do it. (You have been warned.)

Building custom SSIS tasks is neither trivial nor easy. You are building components that will be used to construct software. Perhaps you have done this before. Many have not. So one key is recovering gracefully when the code does not perform as expected. Here’s what I do when that happens:

  • Un-register the assembly from the GAC.
  • Clean the Visual Studio solution.
  • Make another attempt at coding the desired functionality.
  • Build the assembly.
  • Register the assembly in the GAC.

I find these steps get me out of trouble faster than I get myself into it, and that is a good thing. How to accomplish it? First, find gacutil.exe. Like sn.exe mentioned in the post Creating a Custom SSIS 2012 Task – Signing the Assembly, gacutil moves around with each release of the .Net Framework. At the time of this writing, I find the version I want in the C:Program Files (x86)Microsoft SDKsWindowsv8.0AbinNETFX 4.0 Tools folder.

Open the MyFirstTask solution, and then open the MyFirstTaskNotes.txt file. Assuming you substitute the proper paths, add the following text to the file:

— register “C:Program Files (x86)Microsoft SDKsWindowsv8.0AbinNETFX 4.0 Toolsgacutil.exe” -if “<SSIS Tasks subfolder>MyFirstTask.dll” — unregister “C:Program Files (x86)Microsoft SDKsWindowsv8.0AbinNETFX 4.0 Toolsgacutil.exe” -u MyFirstTask

How does this work? The first line – labeled “register” – uses gacutil to register the MyFirstTask dynamic-link library (dll) file with the Global Assembly Cache (GAC). The second line un-registers the same assembly – effectively removing it from the GAC. These comprise the last and first steps (respectively) for recovery from failure listed above. You run these steps in a command window opened with “Run as Administrator” permissions as discussed in the post Creating a Custom SSIS 2012 Task – Signing the Assembly.

With other versions of Visual Studio 2012, you can add pre- and post-build events to execute these commands automagically. If you choose to develop in Microsoft Visual C# in Visual Studio Express 2012 for Windows Desktop, you can also add pre- and post-build events. Just not in Visual Basic. I am unsure why this functionality was not included with Visual Basic 2012 Express. You clean and build the solution from the Build menu in Visual Studio:


Figure 1: The Visual Studio Build Menu

Cleaning is the second step, and Building is the fourth step listed above for recovering from a failed attempt. I often switch steps two and three, diving into a fix, patch, or adding additional functionality before Cleaning and then Building the solution.

Setting The Output Path

The reason we need to open Visual Studio Express 2012 for Windows Desktop in “Run as Administrator” mode is so we can build the output – the dll, or assembly – to a subfolder in the 32-bit Program Files folder. There are other ways to get the assembly into the Program Files subfolder, but I doubt there are many with fewer steps (without using a non-Express edition of Visual Studio or Visual C#).

Locate the SSIS Tasks subfolder used by SQL Server Data Tools to build SSIS packages. It will be located at <installation drive>:Program Files (x86)Microsoft SQL Server110DTSTasks. I installed SQL Server to my E: drive, so my path to the SSIS Tasks folder is: “E:Program Files (x86)Microsoft SQL Server110DTSTasks”. Replace the <SSIS Tasks subfolder> placeholder with the path to your SSIS Tasks subfolder in the “register” command listed above and in your MyFirstTaskNotes.txt file.

For me, register now appears:
— register “C:Program Files (x86)Microsoft SDKsWindowsv8.0AbinNETFX 4.0 Toolsgacutil.exe” -if “E:Program Files (x86)Microsoft SQL Server110DTSTasksMyFirstTask.dll”

In Solution Explorer, open MyProject and click on the Compile page:



Figure 2: The Compile Page

Copy the SSIS Tasks subfolder path into the “Build output path” textbox as shown in Figure 3:

Figure 3: Altering the Build Output Path

With this change, the assembly will be built and delivered to the folder SSDT uses to populate the SSIS Toolbox. Note: This is not the location of tasks that are executed at runtime. The runtime executables must be registered in the GAC.

The Build Process

We now have all the required pieces in place to begin coding our custom task. That’s next! :{>