title: Error opening Xlsx files created from calling Reporting Services Web Service ReportExecution2005
body: <p>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.</p>
  
  <p>Can someone please tell me what I need to do to render a usable xlsx file?</p>
  
  <p>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 ………..”</p>
  
  <p>In my code I’m using</p>
  
  <pre><code>MimeType = “application/vnd.ms-excel” for the xls file
  </code></pre>
  
  <p>And</p>
  
  <p>MimeType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  ” </p>
  
  <p>for the xlsx file</p>
  
  <p>The web service I am using to run the report is ReportExecution2005.asmx?wsdl
  (which I assume is the correct one to use?)</p>
  
  <p>My code from the SSIS Script Task is below,</p>
  
  <pre><code>        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 = @"&lt;DeviceInfo&gt;&lt;Toolbar&gt;False&lt;/Toolbar&gt;&lt;/DeviceInfo&gt;";
  
          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;
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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;
  
  /// &lt;summary&gt;
  /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
  /// or parent of this class.
  /// &lt;/summary&gt;
  [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 = @"&lt;DeviceInfo&gt;&lt;Toolbar&gt;False&lt;/Toolbar&gt;&lt;/DeviceInfo&gt;";
          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;
      }
  
  
  }
  </code></pre>
  
  <p>}</p>