Hi, Today my post is on ‘How to execute and render SSRS report (Excel format) from SSIS package’.
Am assuming a SSRS report (Employee Details) which accepts ‘Department’ as parameter, please have a glance at the Employee table and Report generated for particular department.
I’ll explain you how am going to execute the ‘EmployeeReport’ report from SSIS package.
‘@parameter1’ is the parameter which am passing to the report, please go through the ‘Dataset properties’ figure of SSRS, we need to use the same name in the SSIS package.
Create a SSIS package and Drag Script task into the design pane and before editing change the ‘Script language’ property to ‘Microsoft Visual Basic 2008’
Variables in SSIS package:
User::FileExtension - To generate report with given File extension.
User::FileType - File Format name of given file extension.
User::FullFilePath - To generate file in given file path.
User::Filename - To dynamically name the generated file.
User::Parameter1 - To pass parameter to the SSRS report.
Right click on ‘Script task’ and select ‘Edit’. Select FileExtension, FileType, FullFilepath, parameter1 as ‘ReadOnlyVariables’
After selecting ‘ReadOnlyVariables’, click on ‘Edit Script’.
We need to add ‘ReportExecutionService’ as a web reference, right click on the class name and select ‘Add Web reference’ property.
Paste ‘http://localhost/reportserver/ReportExecution2005.asmx?WSDL’ in the URL box.
In the above URL localhost refers to the server, reportserver refers to the virtual directory. To know the virtual directory name, open Reporting Services Configuration manager –> click on Web Service URL then refer to the Reporting Server Web Service Virtual Directory name, as shown in below figure.
Click on GO button, we’ll get the description regarding ‘ReportExecutionService’ and click on ‘Add reference ‘ after giving some name to the Web reference(‘localhost’ in my example).
Script task Code (Microsoft Visual Basic 2008)
We need to import ‘System.IO’ class to deal with File stream objects, please go through below figure for the Script task code.
‘.LoadReport("/EmployeeReport/EmployeReport", Nothing)’ : ‘/EmployeeReport/EmployeReport’ is where the Report stored in Report Manager.
Report will be generated in the given File path with ‘Report name’ as the ‘Excel sheet name’
Let me know your comments on my Post.