In the first part of this series we generated an SSRS report using an SSIS script task. In the second part we created a custom task to display “Hello World”. In this article we will combine the work to create a custom task to generate an SSRS report. For this post we will simply get the runtime side of the task working. Design time support will be added in a later article.

Changes From Last Time

Before we get started I’ve made a few changes since last time.

1. I updated to the Windows 8.1A SDK and the .NET 4.5.1 tools. This impacts the pre/post-build events for the projects.  Only the path information changed.
2. Rather than using SSDT for Visual Studio 2013 I am now using SSDT Preview for Visual Studio 2015 which allows me to use VS2015 and target different versions of SQL. The solution/project files have been updated accordingly.
3. I changed the SQL query to get the orders to limit it to $150K or more so fewer reports would run which will speed up debugging.

Creating the Task Project

There is a lot of boilerplate code involved in setting up custom tasks so the first thing we want to do is create a set of types to support creating tasks easily. One of the patterns that has emerged from the existing SSIS tasks that you will see is that each task (or related tasks) resides in its own assembly. This makes it easy to update a single task without other tasks that did not change. This is important because tasks go into the GAC and SSIS is going to use the specific version that it was designed with. Updating task assemblies is a maintenance issue so the less often they change the better.

Since we are creating some base infrastructure that all tasks will need we will use the existing project as our core library for shared types. Each task will get its own assembly and we will name them using a simple pattern that can be used when automating the installation. While we can go ahead and move the existing HelloWorldTask to its own project, it was really just for demonstration purposes. Either leave it where it is at or remove it altogether.

Create a new class library project called P3Net.IntegrationServices.Tasks.Ssrs. As we did before set up the project to support SSIS.

1. Framework version = 4.5.2 (You use must use the same version of the framework as is supported by SSIS).
2. Sign the assembly using the .snk contained in the solution.
3. Copy the build events from the existing project into the new project.
4. Update the AssemblyInfo.cs file to contain the relevant information.
5. Add references to required assemblies.
   • P3Net.IntegrationServices (Project)
   • Microsoft.SqlServer.ManagedDTS 12.0 (Framework\Extensions)
   • System.Drawing (Framework)
   • System.Runtime.Serialization (Framework)

Now go ahead and create a new class called GenerateSsrsTask. This will be the task that generates the SSRS report. Since each task has its own assembly all the types needed to support this task can simply be created here. This class needs to be public and will derive from BaseTask which is the base class we will use for all tasks.

BaseTask

BaseTask will derive from the standard SSIS Task class and will be abstract. For now, the only public property it will have is an abstract string representing the task name so that we can use it for logging and other purposes.

public abstract class BaseTask : Task
{
    public abstract string TaskDisplayName { get; }
}

Note: Task is also the name for the standard .NET TPL type which is imported by default in new files. Be sure to remove the using statement for the System.Threading.Task namespace otherwise you will run into name conflicts.

We already talked about the 3 methods that a task must implement as part of creating our first task (initialize, validate and execute). The base class will implement all these methods but we don’t want to expose them directly to custom tasks. The base class will be responsible for implementing them but it will call virtual methods in derived classes to implement the core work. To allow for some flexibility later we will replace all the parameters with a series of interfaces (one for each method group) that allows us to pass all the same information and any extra information we want. Additionally the base class will wrap any exceptions that occur in a consistent manner.

Here is the code for executing the task.

public override DTSExecResult Execute ( Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction )
{
    try
    {
        var context = new TaskExecuteContext()
        {
            Connections = connections,
            Log = log,
            Transaction = transaction,
            Variables = variableDispenser,

            Events = componentEvents
        };

        return ExecuteCore(context);
    } catch (Exception e)
    {
        if (componentEvents != null)
            componentEvents.LogError(b => b.Message("Unhandled exception during execution.")
                                            .Exception(e));
        return DTSExecResult.Failure;
    };
}

protected abstract DTSExecResult ExecuteCore ( ITaskExecuteContext context );

All the methods work similarly.

• Wrap the entire call in a try-catch statement.
• Do any parameter validation.
• Create a context object that contains all the parameters and any additional data the derived type might need.
• Call the derived implementation method.
• If an exception occurs then log the exception and return failure.

Derived types get the ITaskExecuteContext object that we create. The concrete implementation is specific to the infrastructure and therefore is not exposed to derived types. Since each method has different sets of parameters we have separate interfaces and concrete types for each context.

public interface ITaskExecuteContext
{
    Connections Connections { get; }
    IDTSComponentEvents Events { get; }
    IDTSLogging Log { get; }
    object Transaction { get; }
    VariableDispenser Variables { get; }
}

internal sealed class TaskExecuteContext : ITaskExecuteContext
{
    public Connections Connections { get; set; }

    public IDTSComponentEvents Events { get; set; }

    public IDTSLogging Log { get; set; }

    public object Transaction { get; set; }

    public VariableDispenser Variables { get; set; }        
}

Refer to the code for the remaining methods, interfaces and types. For initialization and validation the base type provides an implementation and therefore a derived type does not need to implement it. In most cases however initialization will probably be required.

A Note on Logging

SSIS has several different types for logging.

• IDTSComponentEvents – Used for writing events when a component is executing.
• IDTSInfoEvents – Used for writing information events during initialization.
• IDTSLogging – Used for the logging events from a container.

Depending upon where in the process you are determines which type you’ll use. To simplify access to these objects the code provides some extension methods that provide a consistent set of calls irrelevant of the logging interface. Refer to the code for the implementation.

Implementing the SSRS Task

We can now return to the GenerateSsrsTask and implement the methods that we need. We need to do the following.

1. Create an icon for task
2. Add the DtsTaskAttribute attribute to the type with the appropriate values.
3. Implement the ExecuteCore method.
4. If the task needs to do any validation then implement the ValidateCore method.

For the task icon you can create any icon you want. The icon is shown in the Toolbox and in the designer so it should be clear but note that it cannot be too big. 32×32 (32 bit) and 16×16 (32 bit) are reasonable. Remove any other icons. Note: VS2015 does not support editing 32-bit icons so you will need to use a third party program. We will need the icon for design time support later so go ahead and add the icon file to the Resources editor for the project and ensure the Resources-generated file is marked with Public accessibility.

Adding the DtsTaskAttribute attribute is straightforward but to keep things consistent we will use a series of constant properties that expose the values the attribute needs. We will need similar information during design time later so using properties makes it easy to keep consistent. Add the following members to the task.

/// 
This is the description shown for the task in the designer.
public const string Description = "Generates an SSRS report.";

/// 
This is the name of the task in the Toolbox and the default name in the designer.
public const string TaskName = "P3Net - Generate SSRS Report Task";

public static Icon Icon = Resources.GenerateSsrsTask;

public override string TaskDisplayName
{
    get { return TaskName; }
}

Now we can add the task attribute. Note the resource name reference for the icon. The resource name is set when the resource is inserted and is based upon the default namespace and path to the resource. If anything changes the resource name will remain the same. The easiest way to determine the resource name is to compile the code and then use a disassembler to look at the resources in the assembly.

[DtsTask(DisplayName = GenerateSsrsTask.TaskName, RequiredProductLevel = DTSProductLevel.None, Description = GenerateSsrsTask.Description
        ,IconResource = "P3Net.IntegrationServices.Tasks.Ssrs.Images.GenerateSsrsTask.ico")]
public class GenerateSsrsTask : BaseTask

Finally we are ready to implement the ExecuteCore method. In order to properly implement the method though we need to talk about connections, variables, parameters and persistence. That is much to large for this article so we will simply write a log entry to demonstrate that we’ve done something.

protected override DTSExecResult ExecuteCore ( ITaskExecuteContext context )
{
    context.Events.LogInformation("GenerateSsrsTask called.");

    return DTSExecResult.Success;
}

Updating the SSIS Package

Once the projects compile (remember you must be an administrator) then we can use the task in the designer. After compilation, restart SSDT/Visual Studio where the SSIS package is defined. Disable the existing Generate Report task and open the Toolbox.

Note: Since I have switched to the SSDT Preview I had to change the existing package properties. By default it is targeting SQL 2016 but the tasks we’re creating are targeting SQL 2014. Therefore the package has to be downgraded.

1. Go to the project’s property page.
2. Go to Configuration Properties\General.
3. Set TargetServerVersion to SQL Server 2014.
4. A warning will appear and all the packages will be closed.
5. Reopen the package.

Now our new task should appear. Drag and drop it next to the original task. Run the package and the task should execute. If it doesn’t then debug the task as we talked about previously.

Final Notes

In the solution are the instructions (Creating New Tasks.txt) that we followed for this article. The other document (Installation.txt) contains instructions on how to install the task onto both the SSIS server and any machine that wants to use SSDT.

In the next article we will implement the execute logic for the task.

Download the Code