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
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.