Error opening Xlsx files created from calling Reporting Services Web Service ReportExecution2005

Question

I cannot open an SSRS report rendered as an xlsx file, produced by calling the Reporting Services Web Service ReportExecution2005.asmx?wsdl from an SSIS Script Task. But I can open xls files produced by the same method.

Can someone please tell me what I need to do to render a usable xlsx file?

I am trying to run a Reporting Services report from SSIS using a script task. I need the report to render as an Excel xlsx file. The code I have works if I use an .xls extension, by works I mean it does result in an xls file that can be opened in Excel. But if I change the file extension to xlsx I get a file that can’t be opened, and produces the following error. “Excel cannot open the file because the file format or file extension is not valid. Verify that the file ………..”

In my code I’m using

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

And

MimeType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ”

for the xlsx file

The web service I am using to run the report is ReportExecution2005.asmx?wsdl (which I assume is the correct one to use?)

My code from the SSIS Script Task is 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;

To get this to run as a deployed package the following amendments were made As the deployed package was unable to reference the config file, the bindings for the web service have been coded in the Script Task

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;
    }


}

}

Answer 1

You can't use XLS and XLSX interchangeably - they are completely different formats.

XLS is binary-based format and XLSX is an XML schema-based format - they will be totally different file types. You can test yourself - create an XLS file in Excel, rename it to XLSX and it will error when you try opening it.

The SSRS EXCEL format can be saved to XLS files only.

SSRS 2012 can export to XLSX, but you would need to use the EXCELOPENXML format, not EXCEL.