none
Automatcially Execute Report (with input parametrs) and Save to Excel/PDF RRS feed

  • Question

  • I have an SSIS package that does this in SQL 2008 R2 by means of a Script Task that uses ReportExecution2005. I cannot get this to work in SQL2012. It the script builds without error but reports a compile error when I close the Script Task, with the dreaded red X in the Control Flow window.

    It has occurred to me that perhaps I can do the same thing using SSRS Web Service, supplying the necessary information in the URL. I know how to supply parameter values and tell it to render in Excel (or PDF) format but user action is still required to save the file. Is there a way to provide an output file name in the URL so that the file is automatically saved without even displaying the report?

    I an also looking at PowerShell/ReportExcution2005 as an option and will raised separate post on that subject.


    R Campbell

    Friday, November 14, 2014 11:36 PM

Answers

  • I never did find out why the SSIS package produced an error (or what the error was). I did get the PowerShell script working. Any environment that can host the Reportting Web Services code is fine with me.

    R Campbell

    • Marked as answer by Dick Campbell Tuesday, December 2, 2014 7:05 AM
    Tuesday, December 2, 2014 7:05 AM

All replies

  • Hi Dick Campbell,

    According to your description, you want to export report to Excel/PDF from the a web site and save it without displaying the report, if that is the case, we can use ReportExcution2005.asmx as the report rendering web service.

    SQL Server Reporting Services is essentially implemented as a set of Web services, commonly referred to as SOAP API. In order to use the endpoints from Visual Studio, please refer to the following steps:

    1. Add Web reference to the endpoints.
    2. Then we need to initialize the report by calling the LoadReport() method.
    3. If the report contains parameters, we need to declare and populate an array of ParameterValue objects. Once the parameters are set up, we can call the SetExecutionParameters() method and pass them in rsExec.SetExecutionParameters(executionParams, "en-us").
    4. Call the Render() method. This method renders the report and returns it as a byte array that we can save into a file.

    The following code is for your reference:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using WebApplication3.ReportExecution2005;
    using System.IO;
    
    namespace WebApplication3
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
              
                ReportExecutionService rsExec = new ReportExecutionService();
                rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
                rsExec.Url = "http://servername/ReportServer/reportexecution2005.asmx";
    
                string historyID = null;
                string reportPath = "/multi-value parameter/sub";
                rsExec.LoadReport(reportPath, historyID);
    
                ReportExecution2005.ParameterValue[] executionParams;
                executionParams = new ReportExecution2005.ParameterValue[1];
                executionParams[0] = new ReportExecution2005.ParameterValue();
                
                executionParams[0].Name = "group";
                executionParams[0].Value = "Europe";
                new ReportExecution2005.ParameterValue();
    
                rsExec.SetExecutionParameters(executionParams, "en-us");
    
                string deviceInfo = null;
                string extension;
                string encoding;
                string mimeType;
                ReportExecution2005.Warning[] warnings = null;
                string[] streamIDs = null;
                string format = "Excel";
    
                Byte[] results = rsExec.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
                FileStream stream = File.OpenWrite("c:\\Users\\Reports\\report.xlsx");
                stream.Write(results, 0, results.Length);
                stream.Close();
            }
        }
    }


    For more information, please refer to the following document:
    http://www.experts-exchange.com/Programming/Microsoft_Development/Q_27581973.html
     
    If you have any more questions, please feel free to ask.

    Thanks,
    Wendy Fu


    Saturday, November 15, 2014 11:02 AM
    Moderator
  • Thanks for the code. I have been using a VB equivalent in a SQL Server 2008 SSIS package but it doesn't seem to work in SQL Server 2012. The Script Task builds in SSIS 2012 but reports a compile error when the Script Task is closed, after which it displays with a red X. I end up with a script that compiles (builds) but won't run, with no error message to tell me what the problem is.

    I can see a "Using" declarations in your code (equivalent to "Imports" in VB) that I don't have in my script. I have simply added ReportsExcution2005 as  Web Service Reference (not a Service Reference). I will add them to the script (as Imports statements) to see if that solves the problem.

    Other difference is that the code you supplied, refers to ReportServer2010, in addition to ReportExecution2005 so I will add that too as a Service Reference or a Web Service Reference and in an Imports statement.

    The other approach that I am looking at to use PowerShell. I think that I have a working example that should help me get there. Another example of how this can be done in PowerShell would be appreciated though, particularly one that shows hoe to save to a file, without user interaction.


    R Campbell

    Sunday, November 16, 2014 5:41 AM
  • Hi Dick Campbell,

    According to your description, you have been using VB in SQL Server 2008 SSIS package and it works fine. When you use the code in SQL Server 2012, a compile error occurred, when the Script Task is closed, it displays with a red X. In this case, we can double-click the Script Task, then click Edit Script button to see error message.

    I debugged the code again and find that we do not need to refer to ReportServer2010, sorry for the mistake. Here is a link relevant to Rendering SQL Reporting Reports with PowerShell, you can reference:
    http://blogs.technet.com/b/stefan_stranger/archive/2010/05/16/rendering-sql-reporting-reports-with-powershell.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Wendy Fu

    Tuesday, November 25, 2014 3:20 AM
    Moderator
  • Has this issue been resolved?  I'm having the same problem and would prefer not using PowerShell.  Thanks.
    Tuesday, December 2, 2014 1:13 AM
  • Hi

    Why don't you use subscriptions to generate the report on a shared location or have it emailed.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Tuesday, December 2, 2014 5:47 AM
  • I never did find out why the SSIS package produced an error (or what the error was). I did get the PowerShell script working. Any environment that can host the Reportting Web Services code is fine with me.

    R Campbell

    • Marked as answer by Dick Campbell Tuesday, December 2, 2014 7:05 AM
    Tuesday, December 2, 2014 7:05 AM
  • Hi

    Why don't you use subscriptions to generate the report on a shared location or have it emailed.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     


    I think that you need Enterprise Edition (with table driven parameters) to be able to pass parameter values and even then, it is a bit ugly. I am now quite happy to use PowerShell.

    R Campbell

    Tuesday, December 2, 2014 7:09 AM