Creating an SSIS Custom Task, Part 2

Published on Author Michael

In the last article we created a simple SSIS package to generate an SSRS report and save it to disk. For a single report this is fine as it didn’t require a lot of code but imagine if we wanted to write new packages and reuse this code. This is where script tasks break down. Each script task is a standalone block of code, basically its own .NET project. Code in one script task has no access to any other script, even in the same package. The only way to share code is copy/paste. This is error prone and unmaintainable.

One way to work around this is to create code outside the script task and copy paste the source files into each script task. This works but can be difficult to maintain over time. The ideal solution is to move this code into its own assembly and then reference the assembly in each script. Unfortunately SSIS requires that all script references be in the GAC. This complicates deployment as we’ll see later but is doable.

This is where SSIS custom tasks become useful. When you want to use the same script task in several different places or packages then it is time to promote it to a custom task. This provides several benefits.

  1. Code duplication is eliminated.
  2. The need for a script task goes away and is replaced by a reusable task.
  3. A custom task is easier to use in packages then script tasks.
  4. The custom task can access functionality that is difficult or impossible to do inside a script task.

For this article we will replace the existing script task to generate SSRS reports with a custom task. Creating the task and building the UI is straightforward once you get past the initial learning curve. Surprisingly though working with Winforms proves to be the most challenging aspect of the process. Before continuing be sure that everything is setup as discussed in the previous article.

Setting up the Runtime Project

A custom task consists of two parts: runtime task and design time UI. The runtime task is responsible for managing the task properties, validating the task properties, executing the task and persisting the properties to storage. The design time UI is responsible for providing the UI to the designer to allow the task properties to be set. Technically a task does not need a UI but it makes working with the task easier. Surprisingly it also requires the most work. We will discuss it later.

To get started do the following.

  1. Start Visual Studio 2015 with admin privileges (this is required for debugging).
  2. Create a new solution to contain the SSIS projects we will create (i.e. P3Net.IntegrationServices).

The first project will be the runtime project needed by SSIS to run the task. Since we may want to create multiple tasks we’ll simply call this one P3Net.IntegrationServices. It will contain all the code needed for the runtime task. The steps are given below but there are some rules that should be clarified up front.

  • SSIS 2012/2014 only support CLR 4 so all projects must target .NET 4.x. If an assembly targets a different CLR version then SSIS will not load it.
  • All assemblies must be signed. For this article we will create a single .snk file and use it for all projects.
  • All assembly references related to SSIS must exactly match the version of SSIS being used.

The last rule is very important. The custom tasks are tied to the specific version of SSIS being used. In order to support multiple versions of SSIS you have to create multiple projects (or upgrade existing projects). SQL versioning is odd and discussed here. The quick guide is that SSIS 2012 uses 11.0 and SSIS 2014 uses 12.0. If the version is incorrect then the code will compile but will not be accessible in the designer or at runtime. For this article I am targeting SQL 2014 and will mention 12.0 so adjust accordingly.

  1. Add a new Class Library.
  2. Ensure the framework version is .NET 4 through .NET 4.5.2.
  3. Sign the assembly using Project properties\Signing.
  4. Update the AssemblyInfo.cs file to contain the relevant information.
  5. Add the following assembly references (remember to choose the correct assembly version based upon the target SSIS version).
    1. System.Drawing (Framework)
    2. System.Runtime.Serialization (Framework)
    3. Microsoft.SqlServer.ManagedDTS 12.0 (Framework\Extensions)

If you do not see Microsoft.SqlServer.ManagedDTS then you probably do not have the Client Tools SDK installed from the SQL Server Installation. Ensure that the component is installed before continuing.

Hello World Task

Now we can create our first task, Hello World. In general SSIS tasks should run in isolation so the correct approach is to put each custom task in its own assembly. This goes for the runtime and design time components. But for our purposes we will simply use a couple of folders to separate tasks from each other.

  1. Create new folder in the project called Tasks.
  2. Create a subfolder under Tasks called HelloWorld.
  3. In the subfolder create a new class called HelloWorldTask.
  4. Derive the class from the base type Microsoft.SqlServer.Dts.Runtime.Task. We are going to run into issues with the TPL at this point so remove the threading namespaces.

We need to provide some metadata so the designer can load the task so we will add a DtsTask attribute to the class. This attribute provides basic information about the task, its icon, the UI designer for it and what edition of SSIS we require. 

[DtsTask(DisplayName = "Hello World Task", Description = "A sample SSIS custom task.", RequiredProductLevel = DTSProductLevel.None)]
public class HelloWorldTask : Task
{
}

For now we won’t have an icon or UI designer. We can now implement the core methods needed by SSIS.

InitializeTask is called to initialize the task before it runs. At this point we need to capture any of the host-provided services that we will need to execute our task including the available connections, variables and event subsystem. If we don’t capture the services here we won’t be able to use them later. 

public override void InitializeTask ( Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos, LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker )
{
    m_connections = connections;
    m_variables = variableDispenser;
    m_events = events;

    base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);
}

private Connections m_connections;
private VariableDispenser m_variables;
private IDTSInfoEvents m_events;

Once the task is initialized then Validate is called to ensure the task can run. This is where you would verify that all the required properties of the task are set and perhaps that any variables being used are the correct type. For our simple task we don’t require any validation so we will simply return success. 

public override DTSExecResult Validate ( Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log )
{
    return DTSExecResult.Success;
}

Finally we get to Execute which is where all the work is actually done. For our simple task we will simply log a hello world message to the log file. 

public override DTSExecResult Execute ( Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction )
{
    bool fireAgain = true;
    componentEvents.FireInformation(0, "HelloWorldTask", "Hello World", null, 0, ref fireAgain);

    return DTSExecResult.Success;
}

Deploying Custom Tasks

Our task doesn’t do much but now is the time to see if everything is working correctly. Deploying a custom task is a little tricky. In order for the designer to see the task it has to be deployed to a folder where the designer is looking for custom tasks. But SSIS itself requires the task to be in the GAC. Therefore you end up with 2 different deployment scenarios we have to handle.

For deployment to the SSIS server do the following:

  1. Copy the runtime assembly to the directory where SSIS looks for custom tasks. By default this is probably C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Tasks but you can look at the registry key HKLM\Software\Microsoft SQL Server\120\SSIS\Setup\DTSPath to confirm.
  2. Register the runtime assembly in the GAC (be sure to use the CLR v4 version and not an older one).

To make this easier during development we’ll create build events to handle all this. This is the reason why we need to be running VS as an administrator. First the pre-build event.

if $(ConfigurationName) == Debug (
"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" -u "$(TargetName)"
)

The above code uninstalls the assembly from the GAC. This ensures we are using the latest version. We only do this in debug builds for testing purposes. Now the post-build event.

if $(ConfigurationName) == Debug (
xcopy /R /Y "$(TargetPath)" "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Tasks"
"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" -i "$(TargetPath)"
)

This code copies the assembly to the appropriate location and then installs it into the GAC. Assuming everything builds correctly we can now start SSDT and open our SSIS package from last time.

If everything is working correctly we should see our custom task show up in the SSIS Toolbox. If it doesn’t show up then we likely have a versioning issue with our assemblies or we registered them in the wrong location. Drag and drop the task onto a new package. Since we have no UI then we cannot really interact with the task right now but go ahead and run the package and you should get the Hello World message we added earlier. It will appear under Execution Results in the designer.

Debugging a Runtime Task

Some times things go wrong or you need to step through your runtime task. This is pretty straightforward but probably not something you are used to doing. The issue is that SSDT isn’t actually running your task. It is simply asking SSIS to run it. As such you cannot directly run SSIS and debug your task. To debug your runtime task do the following:

  1. Set a breakpoint in SSDT on your task (or some other task that executes before your task).
  2. Run the package and wait for the breakpoint to be hit.
  3. Switch over to VS where the runtime task solution is loaded and select Debug\Attach to process.
  4. Locate the DtsDebugHost.exe (Managed) process, select it and Attach.
  5. Set a breakpoint in your task code as you normally would (for example in the Execute method).
  6. Switch back to SSDT and continue running.
  7. When the breakpoint is hit you will switch back to VS and can debug normally.

You will need to repeat this process each time you debug.

Next Steps

At this point we have a working runtime task for SSIS that we can debug. In the next article we will start working on a task to generate SSRS reports.

Code