String reportFile = "ProductListing.mht" įileStream stream = File.Create(reportFile, result.Length) ĭts.TaskResult = (int)ScriptResults.Success ĭts.Events.FireError(0, "saving report file", e.Message, "\r" + e.StackTrace, String.Empty, 0) Write the contents of the report to a mht/html/pdf/csv/xlsx/tiff file to be attached in the email
Result = rexec.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs) ĭts.Events.FireError(0, "report rendering", e.Message, "\r" + e.StackTrace, String.Empty, 0) ĭts.TaskResult = (int)ScriptResults.Failure Rexec.SetExecutionParameters(parameters, "en-us") Parameters.Value = () ĮxecutionInfo execInfo = new ExecutionInfo() ĮxecutionHeader execHeader = new ExecutionHeader() ĮxecInfo = rexec.LoadReport(reportPath, historyID) ParameterValue parameters = new ParameterValue String devInfo = Prepare report parameter. String reportPath = "/TestRS/ProductReport" ReportExecutionService rexec = new ReportExecutionService() Initiating report execution properties Type or copy the script (C#) below into the extract report script task. Click Add Reference.Īdd the web service reference namespace (change to your script namespace) Give it Web Reference Name as TestReportExecution2005. In the Add Web Reference window, type or paste the Report Server Web Service URL with ReportExecution2005.asmx at the end of the string and click the arrow button. Click Advanced in the Add Service Reference window, and click Add Web Reference in the Service Reference Settings window. Right click on References and select Add Service Reference. In order to utilize the Report Server Web Service, we need to add a reference.
Open the Reporting Services Configuration Manger, and locate the Report Server Web Service URLs. This task utilize the Report Server Web Service to process and render report hosted in SQL Server Reporting Server via ReportExecution2005 endpoint. Click Edit Script to add the script below. Assign User::DepartmentID to the ReadOnlyVariables and User::ReportFile to ReadWriteVariables. Double click the task to open the editor. The next step is to configure the extract report script task. Add thee variables, User::RecipientEmail, User::RecipientName, User::DepartmentID and map them to index 0, 1, 2 respectively. Select the Variable Mappings on the left pane. Select the User::RecipientResultSet in the ADO object source variable drop down list. Change the Enumerator to Foreach ADO Enumerator. Double click the for each loop container to open the editor.
The next step is to configure the for each loop. This step map the query result to the variable. Select the Result Set, enter 0 in the Result Name and select User::RecipientResultSet as Variable Name. Configure the data source connection and the SQL statement (Make sure the data type is O bject for RecipientResultSet)Ĭonfigure the Extract Email Recipient (Execute SQL Task). In the variables windows, create 5 variables DepartmentID, RecipientEmail, RecipientName, RecipientResultSet and ReportFile like shown below. Create a Foreach Loop Coailntainer below it and create two Script tasks in the container, and rename the tasks to Extract Report and Em Report respectively.
#SSRS DATA TOOLBAR DOWNLOAD#
In this example, I am using Visual Studio 2012 equipped with Business Intelligence for Visual Studio 2012 (SSDT BI) templates ( download here)Ĭreate a Execute SQL Task by dragging one from the SSIS toolbox onto the control flow, and change the name to Extract Email Recipient. In this post, we utilizes SQL Server Integration Service (SSIS) to render SSRS reports and send out email notification based on an external data source.Ĭreate a SSIS project or package with SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). However, the data driven subscription feature is only available on SQL Server Business Intelligence and Enterprise edition. In the previous post, I shows the steps of configuring data driven subscription on the product listing report.
0 kommentar(er)
