vb.net

vb.net script to call SSRS report via URL Error when output file is over a certain size


I've been using the following script with no issues to automate/call SSRS reports from an SSIS package. The issue that I'm experiencing is that when I go back to a certain date (more than 4 months of data) the Excel file seems to only partially populate and when I go to open it I get an error stating "we found a problem with some content in zzzz.xlsx, would you like to try and fix it?" This does not happen when I shorten the date range of the report, it also works fine if I take the generated URL from this script and paste it into my web browser. I'm wondering if there are size limits set? The output file is around 30mb. Again, runs fine pasting the URL this script generates directly into the browser. Any help is greatly appreciated!

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 = 600000000
            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"
        'destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + Dts.Variables("ReportName").Value.ToString + ".xlsx"
        url = Dts.Variables("ReportURL").Value.ToString + Dts.Variables("Location").Value.ToString + Dts.Variables("ServiceArea").Value.ToString + "&StartDate=" + Dts.Variables("START_DATE").Value.ToString + "&EndDate=" + Dts.Variables("END_DATE").Value.ToString + "&RUNTYPE=2" + "&rs:Format=EXCELOPENXML"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Solution

  • it seems that the issue is that the SSRS report generates multiple worksheets. I haven't found out why but it seems as though there is a data limit per worksheet.