Geek Answers Handbook

Error Opening Xlsx Files Created When Calling Reporting Services Web Services ReportExecution2005

Can't I open an SSRS report presented as an xlsx file created when I call Reporting Services ReportExecution2005.asmx? wsdl from the SSIS Script task. But I can open xls files created by the same method.

Can someone tell me what I need to do to display a useful xlsx file?

I am trying to run a Reporting Services report from SSIS using a Script task. I need a report to render as an Excel xlsx file. The code that I have works if I use the .xls extension, by works, I mean that this leads to the creation of an xls file that can be opened in Excel. But if I changed the file extension to xlsx, I get a file that cannot be opened, and gives the following error. "Excel cannot open the file because the file format or file extension is invalid. Make sure the file ..........."

In my Im code using

MimeType = "application/vnd.ms-excel" for the xls file

and

MimeType = "application / vnd.openxmlformats-officedocument.spreadsheetml.sheet"

for xlsx file

The web service that I use to run the report is ReportExecution2005.asmx? wsdl (what do I think is right to use?)

My code is from SSIS Script task below,

        var rsClient = new RSExec.ReportExecutionServiceSoapClient();

        rsClient.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("username", "password");

        rsClient.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

        byte[] result = null;

        string reportPath = "filepath/filename";  
        string format = "EXCEL"; 
        string historyID = null;
        string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

        string encoding = String.Empty; //"";

        string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // Excel2010


        //string mimeType = "application/vnd.ms-excel"; // EXCEL

        string extension = "";
        Warning[] warnings = null;
        string[] streamIDs = null;
        ParameterValue[] parameters = new ParameterValue[1];
        parameters[0] = new ParameterValue();
        parameters[0].Name = "ReportDate";
        parameters[0].Value = "12/11/2013";


        RSExec.ExecutionInfo execInfo = new RSExec.ExecutionInfo();
        RSExec.TrustedUserHeader trustedUH = new TrustedUserHeader();
        RSExec.ExecutionHeader execHeader = new RSExec.ExecutionHeader();
        RSExec.ServerInfoHeader serverInfo = new ServerInfoHeader();

        execHeader = rsClient.LoadReport(trustedUH, reportPath, historyID, out serverInfo, out execInfo);

        rsClient.SetExecutionParameters(execHeader, trustedUH, parameters, "en-us", out execInfo);



        rsClient.Render(execHeader, trustedUH, format, devInfo, out result, out extension, out encoding, out mimeType, out warnings, out streamIDs);

        string filename = @"filepath\filename.xlsx";

        FileStream stream = File.OpenWrite(filename);
        stream.Write(result, 0, result.Length);
        stream.Close();

        Dts.TaskResult = (int)ScriptResults.Success;

, : , - Script

using System;
using System.IO;
using ST_ece9b5f6ee774a84a76c32da60affdef.RSExec;

namespace ST_ece9b5f6ee774a84a76c32da60affdef
{


//using System;
using System.Xml;
using System.Text;
using System.Data;
using System.Web;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
//using System.IO;
using System.ServiceModel;
using System.ServiceModel.Security;
using System.Windows.Forms;

/// <summary>
/// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    public void Main()
    {

        // set Report Name 
        string ReportName = "My Report";


        // all other variables are set from Package Variables
        string ReportDate = Dts.Variables["User::PreviousBusinessDate"].Value.ToString();
        string reportPath = Dts.Variables["User::ReportServerFolder"].Value.ToString() + ReportName;
        string filename = Dts.Variables["User::DestinationFolder"].Value.ToString() + ReportName + "_" + DateTime.Now.ToString("yyyy-MM-dd_hhmmss") + ".xlsx";


        byte[] result = null;
        string format = "EXCELOPENXML"; // "EXCEL"; //"PDF"; 
        string historyID = null;
        string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
        string encoding = String.Empty; //"";
        string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // Excel2010


        string extension = "";
        Warning[] warnings = null;
        string[] streamIDs = null;
        ParameterValue[] parameters = new ParameterValue[1];
        parameters[0] = new ParameterValue();
        parameters[0].Name = "ReportDate";
        parameters[0].Value = ReportDate;    //"12/11/2013";


        ExecutionInfo execInfo = new ExecutionInfo();
        TrustedUserHeader trustedUH = new TrustedUserHeader();
        ExecutionHeader execHeader = new ExecutionHeader();
        ServerInfoHeader serverInfo = new ServerInfoHeader();

        ReportExecutionServiceSoapClient serviceClient = this.GetServiceClient();

        execHeader = serviceClient.LoadReport(trustedUH, reportPath, historyID, out serverInfo, out execInfo);
        serviceClient.SetExecutionParameters(execHeader, trustedUH, parameters, "en-us", out execInfo);
        serviceClient.Render(execHeader, trustedUH, format, devInfo, out result, out extension, out encoding, out mimeType, out warnings, out streamIDs);


        FileStream stream = File.OpenWrite(filename);
        stream.Write(result, 0, result.Length);
        stream.Close();

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    private ReportExecutionServiceSoapClient GetServiceClient()
    {
        BasicHttpBinding binding = this.GetBinding();
        var address = new EndpointAddress("http://myReportServer/ReportServer/ReportExecution2005.asmx");
        var serviceClient = new ReportExecutionServiceSoapClient(binding, address);
        serviceClient.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("username", "password");
        serviceClient.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
        return serviceClient;
    }

    private BasicHttpBinding GetBinding()
    {
        var readerQuotas = new XmlDictionaryReaderQuotas()
        {
            MaxDepth = 32,
            MaxStringContentLength = 8192,
            MaxArrayLength = 16384,
            MaxBytesPerRead = 4096,
            MaxNameTableCharCount = 16384
        };

        var transport = new HttpTransportSecurity()
        {
            ClientCredentialType = HttpClientCredentialType.Ntlm,
            ProxyCredentialType = HttpProxyCredentialType.None,
            Realm = string.Empty
        };

        var message = new BasicHttpMessageSecurity()
        {
            ClientCredentialType = BasicHttpMessageCredentialType.UserName,
            AlgorithmSuite = SecurityAlgorithmSuite.Default
        };

        var security = new BasicHttpSecurity()
        {
            Mode = BasicHttpSecurityMode.TransportCredentialOnly,
            Transport = transport,
            Message = message
        };

        var binding = new BasicHttpBinding()
        {
            Name = "ReportExecutionServiceSoap",
            CloseTimeout = TimeSpan.FromMinutes(1),
            OpenTimeout = TimeSpan.FromMinutes(1),
            ReceiveTimeout = TimeSpan.FromMinutes(10),
            SendTimeout = TimeSpan.FromMinutes(1),
            AllowCookies = false,
            BypassProxyOnLocal = false,
            HostNameComparisonMode = HostNameComparisonMode.StrongWildcard,
            MaxBufferSize = 524288,
            MaxBufferPoolSize = 524288,
            MaxReceivedMessageSize = 524288,
            MessageEncoding = WSMessageEncoding.Text,
            TextEncoding = Encoding.UTF8,
            TransferMode = TransferMode.Buffered,
            UseDefaultWebProxy = true,
            ReaderQuotas = readerQuotas,
            Security = security
        };

        return binding;
    }


}

}

+4
2

XLS XLSX - .

XLS - , XLSX - XML- - . - XLS Excel, XLSX, .

SSRS EXCEL XLS.

SSRS 2012 XLSX, EXCELOPENXML, EXCEL.

+5

EXCELOPENXML . , .

, : string filename = @"filepath\filename.xlsx , SSRS .xlsx. . () , Render ( "EXCEL", //Renderformat,                                    ,                                   ,                                   ,                                   out mimeType,                                   ,                                                                      );

0

More articles:


All Articles