SQLCLR: Publish SSRS reports from CLR stored procedure

Over the past few years, an task i've come across on a frequent basis is how to produce reports from within an application. The scenario is typically something like: SSIS Package imports data, transforms it and then produces output into a table. The user then wants the data to be rendered into excel and made available to them.

I'd really like to see a system T-SQL stored procedure to be able to do this- something along the lines of sys.sp_renderreport @reportname, @format, @location etc

However, for now this doesn't exist so its down to you to make it happen with the tools at your disposal. SSRS provides the reportexecution2005 webservice which you can use to render reports and its fairly straightforward to call this from a CLR stored procedure.

Here is the source code for the CLR stored procedure:

using System;
using System.Data;
using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Collections.Generic;
using System.Xml.Serialization;
using RenderReport.ReportExecution2005;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RenderReport(string reportPath, string filePath, string format, SqlXml reportParams)
{
ReportExecutionService res = new ReportExecutionService();
res.Url = "http://DEVSERVER/reportserver_DEV/reportexecution2005.asmx?wsdl";
res.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string extension;
string encoding;
string mimeType;
Warning[] warnings;
string[] streamIDs;

int i = 0;

XmlDocument paramXML = new XmlDocument();
paramXML.LoadXml(reportParams.Value);
var paramNodes = paramXML.SelectNodes("//reportparams/param");

ParameterValue[] prms = new ParameterValue[paramNodes.Count];

foreach (XmlNode param in paramNodes)
{
prms[i] = new ParameterValue();
prms[i].Label = param.Attributes["label"].Value;
prms[i].Name = param.Attributes["name"].Value;
prms[i].Value = param.Attributes["value"].Value;
i++;
}

res.LoadReport(reportPath, historyID);

// set params if we have any
if (i > 0)
{
res.SetExecutionParameters(prms, "en-us");
}

byte[] results = res.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

// Open a file stream and write out the report
FileStream stream = File.OpenWrite(filePath);
stream.Write(results, 0, results.Length);
stream.Close();
}
};


A couple of things to note,
1) ReportParams need to be set in this format:
<reportparams><param name="MyName"label="My Name"value="Richard"</reportparams>

2) You will need to follow the steps in this KB article.
3) You will find that you need to deploy this using the EXTERNAL ACCESS permission set.
4) EXTERNAL_ACCESS CLR routines will be executed as the SQL Service Account so it will need appropriate permissions if you want to save reports to a remote location.