使用VB.NET脚本调用SSRS报表时大文件损坏问题

你的问题根源在文件流读取与资源管理环节：原代码缓冲区仅256字节，大文件读取时易出现数据截断；同时未正确释放网络流、文件流等资源，且空异常捕获会掩盖读取错误，导致生成的Excel文件损坏。

关键修改点 #

• 用Using语句自动管理各类流资源，确保所有资源被正确关闭释放
• 增大缓冲区至4KB，提升大文件读取效率，降低数据截断风险
• 添加异常日志输出，便于排查读取过程中的错误
• 对URL参数做编码处理，避免特殊字符导致请求异常

修改后的VB.NET代码 #

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics
Imports System.Text

<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 bufferSize As Integer = 4096 ' 缓冲区调整为4KB
        Dim buffer(bufferSize - 1) As Byte
        Dim bytesRead As Integer

        Try
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000 ' 超时设为600秒（原数值过大）
            loRequest.Method = "GET"

            ' Using自动释放响应资源
            Using loResponse As System.Net.HttpWebResponse = CType(loRequest.GetResponse(), System.Net.HttpWebResponse)
                ' Using自动释放响应流资源
                Using responseStream As System.IO.Stream = loResponse.GetResponseStream()
                    ' Using自动释放文件流资源
                    Using fileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
                        Do
                            bytesRead = responseStream.Read(buffer, 0, bufferSize)
                            If bytesRead > 0 Then
                                fileStream.Write(buffer, 0, bytesRead)
                            End If
                        Loop While bytesRead > 0

                        fileStream.Flush()
                    End Using
                End Using
            End Using

        Catch ex As Exception
            ' 记录异常到SSIS日志
            Dts.Events.FireError(0, "SaveFile Error", ex.Message & vbCrLf & ex.StackTrace, String.Empty, 0)
            Throw ' 抛出异常标记任务失败
        End Try
    End Sub

    Public Sub Main()
        Dim url, destination As String
        destination = $"{Dts.Variables("Folder_Destination").Value.ToString()}\{Dts.Variables("ReportName").Value.ToString()}.xlsx"

        ' 对日期参数做URL编码，避免特殊字符问题
        Dim startDateEncoded As String = System.Web.HttpUtility.UrlEncode(Dts.Variables("START_DATE").Value.ToString())
        Dim endDateEncoded As String = System.Web.HttpUtility.UrlEncode(Dts.Variables("END_DATE").Value.ToString())

        url = $"{Dts.Variables("ReportURL").Value.ToString()}{Dts.Variables("Location").Value.ToString()}&SurgeonService=ALL&DaysTillReturn=90{Dts.Variables("ServiceArea").Value.ToString()}&StartDate={startDateEncoded}&EndDate={endDateEncoded}&rs:Format=EXCELOPENXML"

        Try
            SaveFile(url, destination)
            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            Dts.Events.FireError(0, "Main Error", ex.Message, String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub
End Class

额外注意事项 #

• 需在脚本编辑器的「引用」中添加System.Web程序集，用于URL编码功能
• 大文件生成时，确保SSIS任务有足够的内存和磁盘空间支持

内容的提问来源于stack exchange，提问作者gfuller40