Render SSRS Report into PDF file from SSIS package

This solution exemplifies a simple way to render a report into PDF/XLS/CSV file and then email it to a variable list of recipients. The solution uses SSRS, SSIS and some code in C#. This can be used instead of the SSRS subscriptions and offers more flexibility in setting up the list of recipients without the need of having the SQL Server Enterprise version.

Step 1. Create a new SSIS package using Visual Studio 2008/2012/2014. I have tested the same process in all the 3 versions, they have some differences on the way to deploy the packages but that part is not explained in this brief guide.
Step 2. Include the following objects in your new package:

a. Email Recipient. It is an Execute SQL Task used to get the data needed to create the email per each Recipient.
b. Email Report Cycle. It is a Foreach Loop container required to fetch the data for each recipient.
c. Extract and Send Report. It is a Script Task used to render the SSRS report and email it to each Recipient.
d. Expresion Task. It is used to assign a value to a SQL statement that is executed in the last step.
e. Email Succeeded. It is an Execute SQL Task used to store in one table the emails that were succesful for future reference.






Step 3. Define the variables as displayed in the following pic, but please notice that this definition needs to include as well the variables to get the parameters used for your SSRS report that will be rendered in future steps. In this case the report will use the LoginID as a unique parameter.

Step 4. Email Recipient - Open the editor of the first Execute SQL Task and configure it according to your parameters. In this case, the task is receiving five columns: LoginID, UserName, EMail, EMailFrom, and EMailBody, and all the Data Set is loaded into the Object variable named as User::RecipientList. In this list of variables, you need to include as well the values for the parameters needed for your report in SSRS, in this case, the report is using only the LoginID as a parameter.

Step 5. Specify the Data Set destination variable as shown below:

Step 6. Email Report Cycle - Double click on the Foreach object to open the editor. Then follow the screens below to configure it:

Select Foreach ADO enumerator and the User::RecipientList as the ADO source variable. Then move to the section of Variable Mappings and Add each variable in the order they come in your first Execute SQL Task on Step 4.

Step 7. Extract and Send Report - These step will guide you on how to configure the Script Task to render the report, create the PDF file in memory using a Stream, attaching the PDF file to an email and send it out. Double click on the Script Task to open the Editor.

In the ReadOnlyVariables row add the five variables defined previously (LoginID, UserName, EMail, EMailFrom, and EMailBody). Remember that in this step you need to include the variables with values for the parameters required by your SSRS report. Click on the "Edit Script" button and the VS Script Editor for C# will be opened. As you may know, this Script Editor is prefilled with the basic lines requiered by an script, so once opened, follow the instructions described below:

Right-Click on the References section in the Solution Explorer Panel, then select "Add Service Reference..." This will open the following screen:

Click on "Advanced..." This will open the following screen:

Click on "Add Web Reference..." This will open the following screen:

In the URL line specify the following: http://{Replace with your Report Server Web Service URLs}/ReportExecution2005.asmx Then Click on the green icon and if the right URL was specified all the available Web Services will be listed. Name your Web Reference and Click on the "Add Reference" button. This will add the Web Reference to your project and now you are able to use them. Now, copy the following code to your script in the appropiate sections and replace with your own values were asked:

#region
Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Net.Mail;
using System.Web.Services.Protocols;
using {Add the web reference to your Web Service};
#endregion

public void Main()
{
// Initiating report execution properties
ReportExecutionService rexec = new ReportExecutionService();
rexec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rexec.Url = "http://{Replace with your Report Server Web Service URLs}/ReportExecution2005.asmx";

// Render format e.g. MHTML/HTML4.0/PDF/EXCEL/IMAGE/CSV byte[]
result = null;
string reportPath = "{Replace with the SSRS Report Path}";
string format = "PDF";
string historyID = null;
string devInfo = @"False";

// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3];
parameters[0] = new ParameterValue();
parameters[0].Name = "{Replace with the parameter name as it is defined in your report}";
parameters[0].Value = Dts.Variables["{Replace with the variable name defined in your SSIS package}"].Value.ToString();

string encoding = null;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
rexec.ExecutionHeaderValue = execHeader;
execInfo = rexec.LoadReport(reportPath, historyID);
rexec.SetExecutionParameters(parameters, "en-us");
Console.WriteLine("SessionID: {0}", rexec.ExecutionHeaderValue.ExecutionID);
try
{
result = rexec.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
execInfo = rexec.GetExecutionInfo(); Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);
}

catch (SoapException e)
{
Console.WriteLine(e.Detail.OuterXml);
Dts.Events.FireError(0, "Error ", e.Message + "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

//Write the contents of the report to a mht/html/pdf/csv/xlsx/tiff file to be attached in the email
string reportFile = {Replace with your file name or any name convention you want to use} + ".pdf";
try
{
System.IO.MemoryStream ms = new System.IO.MemoryStream(result);
string htmlMessageTo = Dts.Variables["User::EMail"].Value.ToString();
string htmlMessageFrom = Dts.Variables["User::EMailFrom"].Value.ToString();
string htmlMessageSubject = {Replace with the subject you want to use};
string htmlMessageBody = Dts.Variables["User::EMailBody"].Value.ToString();
string smtpServer = {Replace with the name of your smtp Server};
SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, true, smtpServer, ms, reportFile);
ms.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Dts.Events.FireError(0, "Error", e.Message + "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

Dts.TaskResult = (int)ScriptResults.Success;

}

private void SendMailMessage(string SendTo, string From, string Subject, string Body, bool IsBodyHtml, string Server, MemoryStream ms, String reportFile)
{
MailMessage htmlMessage;
SmtpClient mySmtpClient;
htmlMessage = new MailMessage(From, SendTo, Subject, Body);
htmlMessage.IsBodyHtml = IsBodyHtml;
Attachment attach = new Attachment(ms, reportFile, System.Net.Mime.MediaTypeNames.Application.Pdf);
htmlMessage.Attachments.Add(attach);
mySmtpClient = new SmtpClient(Server);
mySmtpClient.Send(htmlMessage);
htmlMessage.Dispose();
}

Step 8. The last two steps of the project are only used to create a new row in a table that stores details of the successful emails, but they are not explained in detail here. But with these steps your files should be good to go.

Step 9. Deploy your package, the deployment process depends on the Visual Studio version you are using.

Step 10. To schedule the execution of this process you can use the SQL Job Agent, create a new job and call your SSIS package that has been previously deployed then schedule your new SQL job to run according to your requirements.

Let me know your comments, questions or findings.

Thanks!