In the last article we set up some infrastructure for creating tasks. We then implemented the shell of the task to generate SSRS reports. In this article we are going to finish up the runtime side of the SSRS task. To do that we need to talk about task parameters, persisting the parameters, getting access to connections at runtime and how to work with variables at runtime.

Defining Task Parameters

Like normal types, anything you want to be able to set at runtime while running your task needs to be exposed as a public property. For generating SSRS reports we are going to need the following properties.

• ServerConnection – Represents the URL and connection attributes to SSRS.
• ReportPath – The path and name of the report in SSRS.
• ReportFormat – The format of the report to generate.

While you can use any type you need, all our parameters are strings so we’ll expose a property for each of them.

Note: We are not yet creating a UI for the task so anything we expose on the task will be accessible. Later, when we add the UI, the properties will not be directly accessed anymore except if used with the Expressions editor discussed later. If you want to hide a property from the editor then use the BrowsableAttribute with a value of false.

public string ReportFormat
{
    get { return m_reportFormat; }
    set { m_reportFormat = value ?? "PDF"; }
}

public string ReportPath { get; set; }

PDF is the most common report format so we will default to that. Report formats are configurable in SSRS so we want to support any that are configured by the server.

Defining Connection Properties

The ServerConnection property represents the SSRS connection and therefore should use the existing SSIS Connection Manager infrastructure. In SSIS all connections are defined at the package or project level. There are various types of connections available including: ADO, ADO.NET, OLEDB, File and HTTP. Since we will be connecting to SSRS using a standard HTTP request we’ll need an HTTP connection. We already set up that connection a while back, we just need to point the custom task to it.

It is important to understand how connections are identified. A connection has both a unique ID (a GUID) and a name. The name is what is shown to the user and the user can change the name of the connection at any time. The connection ID is generated and associated with the connection when it is initially created. Renaming has no impact on the ID. For a task we want to store and use the ID as it allows our task to work even if the connection is renamed. But users do not want to see the ID so whenever we are showing the connection to the user we will use the name. To simplify this process we create a couple of helper methods in the base class to convert between the two.

protected string TryGetConnectionId ( Connections connections, string nameOrId )
{
    return (connections != null) ? base.GetConnectionID(connections, nameOrId) : null;
}

protected string TryGetConnectionId ( string nameOrId )
{
    return TryGetConnectionId(Connections, nameOrId);
}

protected string TryGetConnectionName ( Connections connections, string nameOrId )
{
    return (connections != null) ? base.GetConnectionName(connections, nameOrId) : null;
}

protected string TryGetConnectionName ( string nameOrId )
{
    return TryGetConnectionName(Connections, nameOrId);
}

The two core methods rely on the base SSIS task to do the actual work. The overloads that do not require a Connections object are shortcuts. The Connections type is how tasks get access to the available connections at runtime. It is passed to the various task methods. Our base class captures the value and stores it for easy access. Additionally the context objects we created previously expose it as well. Here’s the code for the connection property.

public string ServerConnection
{
    get { return TryGetConnectionName(m_connectionId) ?? ""; }
    set { m_connectionId = TryGetConnectionId(value); }
}

We store the connection ID but when the property is read/written we use the name.

Defining Variable Properties

Previously we had talked about how variables work in SSIS and the need to lock/unlock them. For our custom task we have similar requirements. To simplify this work we add some extension methods for VariableDispenser and Variables. For lack of a better description, VariableDispenser is how you get access to variables and Variables is the subset of variables you are using. When working with the dispenser we are focused on locking and unlocking the variables. With the variables we are more focused on getting strongly typed values.

Similar to connections, a variable has a user-defined name and a unique ID. When looking at some of the sample tasks from Microsoft they tend to use the name. Like connections though this is going to cause problems if the variable is renamed. We will instead use the same pattern as we did for connections. We will store the ID but the property will always expose the name. So we need to add some new methods to our base class.

protected string TryGetVariableId ( VariableDispenser variables, string name )
{
    if (String.IsNullOrEmpty(name))
        return null;

    return (variables != null) ? variables.GetInfo(name)?.ID : null;
}

protected string TryGetVariableId ( string name )
{
    return TryGetVariableId(Variables, name);
}

protected string TryGetVariableName ( VariableDispenser variables, string id )
{
    if (String.IsNullOrEmpty(id))
        return null;

    return (variables != null) ? variables.GetInfo(id)?.Name : null;
}

protected string TryGetVariableName ( string id )
{            
    return TryGetVariableName(Variables, id);
}

For our SSRS task we need to use a variable to store the content of the report when it is generated. We could use variables for the other properties as well but they will most likely be static. If someone needs to use a variable for them then they can always use an expression instead.

public string Content
{
    get { return TryGetVariableName(m_contentId) ?? ""; }
    set { m_contentId = TryGetVariableId(value); }
}

Report Arguments

One thing that makes SSRS reports a little different is that they support report arguments. At runtime all we really need to know is the name and value for the argument and we can pass it to SSRS. But we’ll want to support a type as well when we add the designer later. Furthermore a report parameter may be a static value (ex. a department name) or it may be something we want to set while SSIS is running (ex. the year). So we want to support both static values and variables as arguments. We will be working with several different properties so it makes sense to create a simple type to represent report arguments. Here is what we’ll use.

public sealed class ReportArgument
{
    public string Name { get; set; }

    public string Value { get; set; }

    public ReportArgumentValueType ValueType { get; set; }
}

public enum ReportArgumentValueType
{
    Variable,
    Value,
}

In the task we’ll expose a collection of report arguments like so.

public List Arguments { get; private set; } = new List();

Supporting both value and variable arguments will impact both the UI when we build it and when trying to get the argument value when we generate the report. We will discuss the latter shortly.

Validating Properties

Now that we have properties we need to go ahead and validate them. Before a task is run, but after the designer is done, a task can validate itself. If the validation fails then SSIS will not run the task. For our task we need to ensure that the connection is still valid and that the report path is set. So we add the ValidationCore method.

protected override DTSExecResult ValidateCore ( ITaskValidateContext context )
{
    //Validate the connection
    var cm = context.Connections.TryGetConnection(ServerConnection);
    if (cm == null)
    {
        context.Events.LogError("Server connection could not be found.");
        return DTSExecResult.Failure;
    };

    //Validate report path
    if (String.IsNullOrWhiteSpace(ReportPath))
    {
        context.Events.LogError("Report Path is required.");
        return DTSExecResult.Failure;
    };            

    //Validate the content variable exists
    if (!String.IsNullOrEmpty(m_contentId))
    {
        var variable = context.Variables.GetInfo(m_contentId);
        if (variable == null)
        {
            context.Events.LogError("Content variable not found.");
            return DTSExecResult.Failure;
        }
    };

    return base.ValidateCore(context);
}

When we add the designer in the next post we’ll see how to make this work nicely such that users can browse existing connection and variables or create new ones just like a standard task. In this particular validation we are simply ensuring the connection and variables exist. If the types or values are wrong we will generate an error when the task is executed.

Persisting Task Parameters

When task parameters are set in the designer they need to be serialized to the XML package file. Out of the box simple, primitive types are automatically serialized. For more complex parameters, like the report arguments, you have to implement IDTSComponentPersist. This interface exposes one method to load the XML and another to save it. Ideally we could implement this in the base class but loading and saving XML depends upon the structure at hand. So I have elected to push the loading/saving to the tasks that need it.

Under the hood each task gets an element in the package file. The element contains the information needed by SSIS to create the task instance. Parameters are stored in a child element. The task can store whatever it wants but most tasks tend to store properties as either attributes or elements.

Here is the implementation for the SSRS task.

public void LoadFromXML ( XmlElement node, IDTSInfoEvents infoEvents )
{
    Content = node.GetAttributeValue("Content");
    ReportFormat = node.GetAttributeValue("ReportFormat");
    ReportPath = node.GetAttributeValue("ReportPath");
    m_connectionId = node.GetAttributeValue("ServerConnection");

    var elements = node.SelectNodes("Arguments/Argument").OfType();
    foreach (var element in elements)
    {
        var arg = new ReportArgument()
        {
            Name = element.GetAttributeValue("name"),
            Value = element.GetAttributeValue("value"),
            ValueType = Parse(element.GetAttributeValue("valueType"), ReportArgumentValueType.Variable)
        };

        Arguments.Add(arg);
    };
}

public void SaveToXML ( XmlDocument doc, IDTSInfoEvents infoEvents )
{
    var root = doc.CreateAndAddElement(GetType().Name);

    root.SetAttributeValue("Content", Content);
    root.SetAttributeValue("ReportFormat", ReportFormat);
    root.SetAttributeValue("ReportPath", ReportPath);
    root.SetAttributeValue("ServerConnection", m_connectionId);

    var element = root.CreateAndAddElement("Arguments");
    foreach (var arg in Arguments)
    {
        var argumentElement = element.CreateAndAddElement("Argument");
        argumentElement.SetAttributeValue("name", arg.Name);
        argumentElement.SetAttributeValue("value", arg.Value);
        argumentElement.SetAttributeValue("valueType", arg.ValueType);
    };
}

A root element is created based upon the task type and the core properties are stored as attributes. The report arguments are stored as child elements. One thing to keep in mind when loading and saving the XML is that of backwards compatibility. Care should be taken if new properties are added or existing properties are removed. The persisted data should be loaded otherwise the user would have to reenter all the properties again before they could save.

Generating the SSRS Report

We can now add the code to actually generate an SSRS report.

protected override DTSExecResult ExecuteCore ( ITaskExecuteContext context )
{
    //Build the query string      
    var reportPath = HttpUtility.UrlEncode(ReportPath.EnsureStartsWith("/"));
    var builder = new StringBuilder(reportPath);

    foreach (var arg in Arguments)
    {
        string strValue = arg.Value;

        //Get the actual value
        if (arg.ValueType == ReportArgumentValueType.Variable)
            strValue = context.Variables.GetValue(arg.Value)?.ToString();

        if (strValue == null)
            builder.AppendFormat("&{0}:isnull=true", HttpUtility.UrlEncode(arg.Name));
        else
            builder.AppendFormat("&{0}={1}", HttpUtility.UrlEncode(arg.Name), HttpUtility.UrlEncode(strValue));
    };
    builder.AppendFormat("&rs:Command=Render&rs:Format={0}", ReportFormat);

    //Get the connection
    var cm = context.Connections.GetConnection(ServerConnection);

    //Create a copy of the connection because we're going to change the URL
    var conn = new HttpClientConnection(cm.AcquireConnection(context.Transaction)).Clone();
    if (conn == null)
        throw new Exception("Unable to acquire connection.");

    // Configure Full Report Url
    var uri = new UriBuilder(conn.ServerURL) { Query = builder.ToString() };
    conn.ServerURL = uri.Uri.ToString();

    // Generate Report            
    context.Events.LogInformation(b => b.Message("Generating report: {0}", conn.ServerURL));
    try
    {
        var data = conn.DownloadData();
        if (!String.IsNullOrWhiteSpace(Content))
            context.Variables.SetValue(Content, data);

        context.Events.LogInformation(b => b.Message("Report generated with size {1}: {0}", conn.ServerURL, data?.Length));
    } catch (Exception e)
    {
        throw HandleCommonExceptions(e);
    };

    return DTSExecResult.Success;
}

Ultimately this code just boils down to converting the parameters to the corresponding query string value. One thing to be very careful of with SSIS connections is when you are using them in parallel. By its very nature SSIS tasks can run in parallel. If you have multiple tasks using the same connection at the same time then you have to ensure that the connection object is not modified. If it is then you will see odd behavior. In this post I discussed the issues with it and the workaround. We’ll use the workaround here because we need to modify the URL in order to send the parameters to SSRS.

The Content property is used to store the generated report if it is set. Sometimes it makes sense to generate a report but you do not really need to capture it so this property is not required. If you want to always require the property then modify the ValidationCore method.

When processing the report arguments notice the check for whether it is a value or variable. In the case of a value we simply grab the argument’s string value and pass that as the argument. For a variable we look up the variable by name and use its current value. We do not convert between IDs and names here although we could have. This is left as an enhancement to the reader.

The HandleCommonExceptions method (which could be an exception filter if you like) handles some common exceptions. Specifically it handles the case where the report does not exist. This makes it easier to diagnose issues with the task.

Testing the Task

At this point we have a fully functioning runtime task to generate SSRS reports. Building the task (remember you need to build as an administrator) should register the task. Now start SSDT (or SSDT for Visual Studio 2015) and load the package. Since we are now persisting the task explicitly you will probably get an error about it when loading. This is fine so just delete the task from the designer and add a new one.

If you double click the task in the designer you will get an error about it not having a UI. We will be adding the UI later. For now bring up the Properties window and then select the task to edit the properties. Notice that there is a validation error already. The ValidationCore method has run already so you should get errors about required properties missing values. Set the properties accordingly.

• ServerConnectionshould be set to the appropriate HTTP connection
• ReportPath can be set to any report (full path from URL) but we’ll use AdventureWorks/Sales_Order_Detail
• ReportFormat should be set to PDF
• Content should be set to a variable that is declared as a byte array and should probably be scoped to the for each container we are in

For the arguments we will use the collection editor to add arguments. We need an argument for the SalesOrderIDStart and SalesOrderIDEnd report parameters. In both cases the existing User::SalesOrderId variable will work.

Once the core properties are set the validation errors should go away. We can now run the code but we won’t see the output because it is being stored in a variable so add another script task after the report task (be sure to hook up the green success constraint). The script task will need to read the variables representing the current sales order ID and the report content. It will then save the report into the folder specified by the target folder connection we created previously. The script is basically a replica of our original generate script that we started with minus all the SSRS logic in the middle.

public void Main()
{
    var salesOrderId = Dts.Variables["User::SalesOrderId"].Value;
    var content = Dts.Variables["User::ReportContent"].Value as byte[];

    //Save the file
    var file = Dts.Connections["TargetFolder"];
    var target = file.AcquireConnection(Dts.Transaction) as string;

    File.WriteAllBytes(Path.Combine(target, salesOrderId.ToString() + ".pdf"), content);           
    Dts.TaskResult = (int)ScriptResults.Success;
}

Running the package now should generate a separate report for each of the sales order just like it did when we started. But now we can generate any report without the need for the scripting we used to have to do.

Next

We are done with the runtime task. In the next article we will add a UI to the designer so we can more easily work with our task.

Download the code.