In a data warehousing/BI solution, sometimes we need to generate reports automatically after data load is complete, and store them in a predefined location. I wish there was an out of box task/component in SSIS to invoke SSRS reports after the data is loaded, but we do not have it yet.
In this article we will discuss how to generate canned reports (SSRS Reports) from a SSIS package. We will discuss the approach to execute one or more SSRS reports and store them in a specific location. The code to do this will be stored in a SSIS package.
First we will create a table in the database to store metadata for report execution. Then we will use a "Script Component" in SSIS package to call the web services exposed by SSRS.
Create a table in the database to store the metadata for report execution. This table will contain report name, path of report on SSRS server, format in which report needs to be executed and location where the report should be placed after execution. Below is the script to create the table:
CREATE TABLE [dbo].[CannedReports]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](20) NULL, [path] [varchar](100) NULL, [reportformat] [varchar](10) NULL, [folderPath] [varchar](200) NULL, [Extension] [varchar](10) NULL ) ON [PRIMARY]
After the table is created, create a data flow task in the SSIS package from where you want to run the report. This data flow task will have an OLEDB/ADO.NET source component to read the data from the CannedReports table.
Now we will call an SSRS web-service to invoke the report and place it in the desired location using a Script component. Add a Script component to the data flow task. The workflow will look like below:
Edit the script component by right clicking and selecting Edit Script. In the code window, the first thing we need to do is generate a proxy class for the SSRS web-service. We will use the ReportExecution service to invoke the report. The standard path of this service on SSRS server is /ReportExecution2005.asmx
To generate a proxy class, right click on references in the Solution Explorer and choose “Add Service Reference” or “Add Web Reference” (depending on the version of Visual Studio). Add a reference using URL of ReportExecution service as shown in screenshot below.
Once the proxy class is generated, you can create an instance of the “ReportExecutionService” class. We create an object of this class at the class level in the script component since we need to use it in different methods of the class. Set the general properties of web-service in the PreExecute method of the script component. For example, I have set the URL, Credentials and Timeout properties in this event as shown below:
rs.Url = "http://localhost/ReportServer_MSSQL2012/reportexecution2005.asmx"; rs.Credentials = System.Net.CredentialCache.DefaultCredentials; rs.Timeout = 72000;
Now edit the ProcessInputRow method in Script component. This event will contain the actual code to call SSRS report and store the output in a file location. Below is the code used in this event:
public override void Input0_ProcessInputRow(Input0Buffer Row) < /* * Add your code here */ try < string extension, mimetype, encoding; string[] streamIds; Warning[] warnings; byte[] results; rs.LoadReport(Row.path + Row.name, null); results = rs.Render(Row.reportformat, null, out extension, out mimetype, out encoding, out warnings, out streamIds); System.IO.FileStream writer = System.IO.File.OpenWrite(Row.folderPath + "\\" + Row.name + "." + Row.Extension); writer.Write(results, 0, results.Length); >catch (Exception e) < >>
In the above code, we are first creating the objects needed to store the data. The rs.LoadReport function loads the SSRS report using the path and reportname stored in the database. "rs.Render" runs the SSRS report and returns the report in form of byte array. The byte array "results" are then written to actual file using the FileStream object.
I have attached the package along with this article. Please note that this package is created using Visual Studio 2010 and uses web-services of SSRS 2012.
Below is some example data that I used to test this approach. If the number of reports or export formats increase, all you need to do is add rows to this table and code will take care of rest of the stuff.
id
Name
path
reportformat
folderPath