Bulk Report Generation using SSIS and SSRS 2008 R2
By Siddharth Mehta, Senior Technical Consultant for Business Information Management unit at Capgemini India Private Limited (Mumbai branch). You can read Siddharth Mehta’s blog at* http://siddhumehta.blogspot.com, and you can contact him at siddhumehta_brain@yahoo.co.in*.
Introduction
This article explains how to programmatically generate reports in large volumes, using SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS) 2008 R2. SSRS is used to develop parameterized reports, and SSIS drives the report generation using the SSRS Web service. A working knowledge of SSIS and SSRS is assumed.
Case Study
Before we start discussing about the implementation details, it would be helpful to understand a practical scenario for programmatically generating reports in large volumes.
Consider a scenario where the IT department of an organization needs is generate a large volume of parameterized, expense reports and email the reports to a varying list of employees that do not have Active Directory accounts. The reports need to be generated and emailed on-demand. As this information is generated every month, the reports should contain only data that is related to the details of the month for which payroll has been processed.
Using SSRS alone to generate and email these reports has limitations. A data-driven subscription is required to email the reports to the users using E-Mail Delivery extension. The data-driven subscription requires a fixed schedule that triggers the delivery of the reports, in contrast to the requirement where report generation and delivery needs to be done on demand.
Using the SSRS and SSIS solution described in this article, a subscription and schedule are not required. The IT department of the organization can generate and email the parameterized reports on-demand to these employees who do not have Active Directory accounts. In addition, the reports are generated and delivered in multiple formats.
Solution Approach
For the sake of discussion, let’s say that the organization has an Enterprise version of SQL Server 2008 R2 installed, and SSRS is installed in native mode with default values. We take it for granted that the user has sufficient privileges to administer and develop SSIS and SSRS on the development machine.
We’ll develop a solution prototype that serves as a Proof-Of-Concept (POC) for our approach on generating bulk reports using SSIS and SSRS 2008 R2. To develop a solution prototype, we need two artifacts for our solution. First, we need a SSRS report that displays data based on some parameter(s) and is used as a model to generate different reports. Second, we need to create a SSIS package that generates the reports, saves reports in multiple formats, and emails the reports using the Send Mail task.
SSRS 2008 R2 provides access to the full functionality of the report server through the Report Server Web service. The Web service provides two endpoints, for native mode and SharePoint integrated mode. In native mode, we use the ReportExecution2005 endpoint and the ReportService2005 endpoint. The “2005” suffix is used in SSRS 2008 R2
The ReportExecution2005 endpoint contains features that facilitate report processing and rendering, and the ReportService2005 endpoint manages objects on a report server. Our intention and requirement is to just execute any report that would be deployed on report server, and save that report in the format of choice. So we only need to use the ReportExecution2005 endpoint.
Prototype Scenario
The prototype scenario concerns a collection of records about some of the most famous buildings across the globe. We need to generate report output based on predefined configuration settings stored in a user-defined configuration table. This would be a parameterized report, and we would consider some parameter as the criteria for generating different reports.
For the dataset, we create a table named Address and populate it with data, as shown below.
Figure 1
Prototype Development Part I: Parameterized Report Development
Create a parameterized report that is used as a model to generate different report outputs based on the “Continent” parameter, from SSIS.
To create the parameterized report:
- Create a new blank report using Business Intelligence Development Studio (BIDS) and name it "FamousBuilding". Name the SSRS solution or project “MyFirstR2ReportProject”.
- Add a data source to the report that connects to the database that hosts the Address table. Name the data source MyDataSrc.
- Add an embedded data source to the report, and formulate the query to retrieve all fields from the Address table. Name this dataset MyDataSet.
- Add another embedded data source to the report, and formulate the query to retrieve distinct continents from the Ad Name this dataset DSContinent.
**Figure 2
5. Add a new report parameter of text data type and name it Continent. Modify the properties on the Available Values page of the Report Parameter Properties dialog to use the values from the dataset we developed in Step 4.
Figure 3
6. Modify the MyDataSet data source to add a filter to it as shown below.
Figure 4
7. Add a table control to the report, and add all the fields except the Continent field from the MyDataSet data set.
8. Deploy the report. If all the above steps were followed correctly, your report should look like the below image.
This completes the development of the report that we will use as a model to create different report outputs based on the Continent parameter.
Figure 5
Prototype Development Part II: SSIS Package Development
Before we create the SSIS package, we need to consider how we will access the Report Server Web service. Though you can use the Web Service task, in my view, a more generic or versatile method is to create a proxy class using the wsdl (web service description language) file, which describes the Web service, and the SSIS Script transformation.
This is the standard way of accessing a Web service in the application programming paradigm, which can be applied in SSIS as the Script transformation is capable of exploiting the potential of .NET framework.
To create a C# (.cs) proxy class:
Using the WSDL tool that ships with .NET Framework, execute the following command from the Visual Studio command prompt.
wsdl /out:C:/ReportService.cs http://[servername]/ReportServer/ReportExecution2005.asmx?wsdl
Next, to store information about the reports that we need to generate, create a table named ReportConfig and populate with the data of your choice. Let’s consider a scenario where the corporate policy needs to send reports in Microsoft Office, Adobe Acrobat and HTML formats so that the recipients of the report can view the report with whatever applications that they may have permissions to access such as Microsoft Word, Microsoft Excel, Adobe Acrobat Reader or a Web browser such as Internet Explorer. In order to meet this requirement, we need to generate reports in .XLS, .DOC, .PDF and .MHTML format for each distinct continent parameter value.
We have 13 distinct values for the continent field in our Address table, and 1 record per format for all the 13 continent values. This requires 52 records, as shown in the figure below.
A separate process would be run to update the email addresses in the email field, by using the organization’s current list of employees stored in a separate table.
Figure 6
In the ReportPath field, the report path should include the root node. For example, I have a FamousBuilding report stored in the MyFirstR2ReportProject folder located under the root node, so the report path is /MyFirstR2ReportProject/FamousBuilding.
To create and run the SSIS package:
- Create a new SSIS package using BIDS.
- Add a Data Flow task, and then add an OLE DB source and configure the source component to read data from the ReportConfig table.
- Add a Script component, pre-configure the component as a transformation, and connect it to the OLE DB source.
- Confirm that the ScriptLanguage property is set to Microsoft Visual C# 2008. On the Input Columns page of the Script Transformation Editor, select all the available input columns.
- Edit the script to add references to the System.Web.Services and System.Xml assemblies, and to add the C# proxy class.Build the code.
- Add the code shown below, to the Script component.
This code will create reports in bulk by making a call to the ReportExecution2005 endpoint. To optimize performance, all initialization is moved out of the ProcessInputRow method that gets executed for each row. In this method, we only make a call to the endpoint to execute the report after setting the parameter value. Authentication settings are set only once during the initialization process.
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string format = "";
//Create Object of ReportExecutionService class
ReportExecutionService rs = new ReportExecutionService();
//Set empty variables required for Render report method
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
Warning[] warnings = null;
string[] streamIDs = null;
string deviceInfo = null;
public override void PreExecute()
{
base.PreExecute();
//Set ReportPath and Authentication Settings
//Replace <servername> with the name of your report server
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = @"http://\<servername\>/ReportServer/reportexecution2005.asmx";
}
public override void PostExecute()
{
base.PostExecute();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
//Load Report
rs.LoadReport(Row.ReportPath , null);
//Create Continent Parameter and assign it's value
ParameterValue[] paramval = new ParameterValue[1];
paramval[0] = new ParameterValue();
paramval[0].Name = "Continent";
paramval[0].Value = Row.ParamValue.ToString();
//Add continent parameter to list of execution parameters
rs.SetExecutionParameters(paramval, "en-us");
//Set the export format
format = Row.ReportFormat.ToString();
//Execute the Report
results = rs.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
//Save the file
using (FileStream stream = File.OpenWrite(Row.OutputPath + @"\\" + Row.ParamValue.ToString() + Row.Extension.ToString() ))
{
stream.Write(results, 0, results.Length);
}
}
}
7. Add a For Each Loop container to the package and add a Send Mail task to the container. Configure the SmtpConnection, From, To, and Attachment properties, and optionally other component properties, to email the generated reports to users. The Send Mail task picks up the email addresses from the Email field in the ReportConfig table and picks up the reports from the folder specified in the OutputPath field in the ReportConfig table.
8. Make sure that the folder specified in the OutputPath field of the ReportConfig table exists, and then run the package.
When the package completes, the reports that were generated based on the data in the ReportConfig table and are now available in the specified folder, are emailed to users.
Figure 7
9. Open these reports. You should be able to see that different reports are generated based on the Continent parameter.
Figure 8
Conclusion
This article has described how to generate and email a large volume of parameterized reports, on-demand to employees who don’t have Active Directory accounts. In addition, the reports are generated and delivered in multiple formats. This solution can be implemented as an SSIS custom component, which would help to address a variety of requirements for programmatic report generation across the enterprise.
About the Author. Siddharth Mehta specializes in architecting business intelligence solutions using Microsoft Business Intelligence related technologies. He is a MCSD in Microsoft .Net 1.1 and MCTS in SQL Server 2005 Implementation and Maintenance, SQL Server 2005 Business Intelligence Development and Maintenance and Microsoft Office Performancepoint Applications. He frequently authors articles on MS BI and SQL Server related topics on community websites like MSSQLTips.com, SQLServerCentral.com, SQLServerPerformance.com and others. He can be reached at siddhumehta_brain@yahoo.co.in