VBScript (vb.net) to save file as .xlsx not .xls

Question

I have an ssis package that calls an ssrs report and it works fine with the exception of how it's saving the excel output.

simply changing the file extension to xlsx does not work, it corrupts the file. I've seen examples where people use ",51" at the end of the filename but I'm not sure where to apply that in my specific code. Does anyone have any idea?

Thank you so much in advance!

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>
<System.CLSCompliantAttribute(False)>
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
        Try
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub
    Public Sub Main()
        Dim url, destination As String
        destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + Dts.Variables("ReportName").Value.ToString + Dts.Variables("OutPutDate").Value.ToString + ".xlsx"
        url = Dts.Variables("ReportURL").Value.ToString + Dts.Variables("Location").Value.ToString + "&DateType=" + Dts.Variables("DateType").Value.ToString + Dts.Variables("ServiceArea").Value.ToString + "&StartDate=" + Dts.Variables("START_DATE").Value.ToString + "&EndDate=" + Dts.Variables("END_DATE").Value.ToString + "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Answer 1

The solution was found here:

https://learn.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-ver16

&rs:Format=EXCELOPENXML

It turned out that it really had nothing to do with the VB but instead, the URL used to run the report and the output format available on the report server.