SQL Server Agent作业执行SSIS脚本任务时间歇性空引用异常

在SQL Server Agent作业中运行的SSIS包会间歇性失败，报错为“Object reference not set to an instance of an object”，错误来源为脚本任务。后续每次运行都会失败，直到在同一机器的Visual Studio 2022中打开该包并运行，之后作业通常会恢复正常，直到下次故障出现。有时重启服务器可解决问题，但并非总能奏效。

补充信息 #

• 该包由2014版本（VS2013数据工具）升级而来
• Visual Studio项目采用包部署模型
• SQL Server数据库版本为2022

用户问题 #

请问这是什么原因导致的？有没有办法在作业运行时调试脚本任务？

如前所述，我可以通过在Visual Studio中手动运行包解决问题，但该包需要每小时通过作业自动运行。

脚本任务代码 #

Public Sub Main()
    Dts.Variables("User::ConsignmentStatus").Value = "OK"
    Dim xml As New StringBuilder()
    xml.Append("<TrackingRequest>")
    xml.Append("<RequestLine>")
    xml.Append("<TrackingNumber>" & Dts.Variables("User::CurrentTrackingNo").Value.ToString & "</TrackingNumber>")
    ' xml.Append("<TrackingNumber>XXXXX</TrackingNumber>")
    xml.Append("<TrackingType>consignment</TrackingType>")
    xml.Append("</RequestLine>")
    xml.Append("</TrackingRequest>")

    ' Create POST data and convert it to a byte array.
    Dim encoding As New UTF8Encoding
    Dim bytes As Byte() = encoding.GetBytes(xml.ToString)
    ServicePointManager.SecurityProtocol = SecurityProtocolType.SystemDefault
    Try
        Dim req As HttpWebRequest = DirectCast(WebRequest.Create(Dts.Variables("User::TrackingWeb").Value.ToString), HttpWebRequest)
        req.Method = "POST"
        req.UseDefaultCredentials = False
        req.Proxy = CType(Nothing, IWebProxy)

        ' Set the ContentType property of the WebRequest.
        req.ContentType = "Application/xml"
        req.Accept = "Application/XML"

        req.KeepAlive = False
        req.ServicePoint.Expect100Continue = False
        req.PreAuthenticate = False

        req.Headers.Add("Authorization", "Basic " + Dts.Variables("User::AuthKey").Value.ToString)

        req.ContentLength = bytes.Length
        ' Get the request stream.
        Using dataStream As Stream = req.GetRequestStream()
            dataStream.Write(bytes, 0, bytes.Length)
        End Using
        ' Get the response.
        Dim response As HttpWebResponse = DirectCast(req.GetResponse(), HttpWebResponse)
        'Dim response As HttpWebResponse = req.GetResponse()
        If (response.StatusCode = HttpStatusCode.OK) Then
            Dim dStream As Stream = response.GetResponseStream()

            Dim reader As New StreamReader(dStream, True)
            Dim responseFromServer As String = reader.ReadToEnd()

            Dim WebResponse As New XmlDocument
            WebResponse.LoadXml(responseFromServer)

            If WebResponse.SelectSingleNode("TrackingResponse/TrackingInfo") IsNot Nothing Then
                Dts.Variables("User::ConsignmentStatus").Value = WebResponse.SelectSingleNode("TrackingResponse/TrackingInfo/ConsignmentStatusDescriptive").InnerText.ToString
                Dts.Variables("User::ConsignmentStatusDate").Value = Convert.ToDateTime(WebResponse.SelectSingleNode("TrackingResponse/TrackingInfo/ConsignmentStatusDate").InnerText.ToString)
            ElseIf WebResponse.SelectSingleNode("TrackingResponse/TrackingErrorInfo") IsNot Nothing Then
                Dts.Variables("User::ErrorDescription").Value = WebResponse.SelectSingleNode("TrackingResponse/TrackingErrorInfo/TrackingErrorDetail/ErrorDetailCodeDesc").InnerText.ToString
                Dts.Variables("User::ErrorCode").Value = WebResponse.SelectSingleNode("TrackingResponse/TrackingErrorInfo/TrackingErrorDetail/ErrorDetailCode").InnerText
                Dts.Variables("User::ConsignmentStatus").Value = "FAIL"

            End If
            reader.Close()
            dStream.Close()
        End If
        response.Close()
        Dts.TaskResult = ScriptResults.Success
    Catch ex As Exception
        Dts.Variables("User::ConsignmentStatus").Value = "FAIL"
        Dts.Variables("User::ErrorCode").Value = ex.HResult.ToString
        Dts.Variables("User::ErrorDescription").Value = ex.InnerException.ToString
        Dts.TaskResult = ScriptResults.Failure
    End Try
End Sub

问题原因分析 #

1. 空引用风险未处理
   代码中多处存在未做空值检查的操作：

   • 直接访问Dts.Variables的Value属性，若变量未加载或值为null会触发异常
   • WebResponse.SelectSingleNode返回null时，直接访问InnerText会引发空引用
   • 异常捕获中直接访问ex.InnerException，若该对象本身为null也会报错

2. 版本兼容性与缓存问题
   包从SSIS 2014升级到2022后，脚本任务的.NET依赖版本可能与SQL Server Agent运行环境不匹配；同时升级后的脚本程序集可能在SSIS运行时缓存中存在冲突，导致对象初始化失败。VS中运行时会重新编译脚本，生成适配当前环境的程序集，从而临时解决问题。

3. 运行上下文差异
   SQL Server Agent服务账户与VS运行时的用户账户在权限、环境变量上存在差异，可能导致脚本任务访问网络、变量等资源时出现未预期的初始化失败。

作业运行时调试脚本任务的方法 #

1. 增强脚本日志输出 #

修改脚本，在关键步骤添加详细日志，写入文件或数据库，定位具体错误位置：

Catch ex As Exception
    Dts.Variables("User::ConsignmentStatus").Value = "FAIL"
    Dts.Variables("User::ErrorCode").Value = ex.HResult.ToString
    ' 拼接完整错误信息，避免InnerException为空
    Dim errorMsg As String = $"错误信息: {ex.Message}{vbCrLf}堆栈: {ex.StackTrace}"
    If ex.InnerException IsNot Nothing Then
        errorMsg &= $"{vbCrLf}内部错误: {ex.InnerException.Message}{vbCrLf}内部堆栈: {ex.InnerException.StackTrace}"
    End If
    Dts.Variables("User::ErrorDescription").Value = errorMsg
    ' 写入本地日志文件
    My.Computer.FileSystem.WriteAllText("C:\SSISLogs\ScriptTaskError.log", $"{DateTime.Now:yyyy-MM-dd HH:mm:ss}: {errorMsg}{vbCrLf}", True)
    Dts.TaskResult = ScriptResults.Failure
End Try

2. 启用SSIS包日志 #

在SSIS包中启用日志记录，选择脚本任务的详细事件，将日志保存到SQL Server表或文本文件，获取作业运行时的完整执行细节。

3. 附加调试器到SQL Server Agent进程 #

• 打开VS2022，选择「调试」->「附加到进程」，找到SQLAGENT.EXE进程并附加
• 在脚本任务的关键代码行设置断点
• 触发作业运行，当执行到断点时检查变量值、对象状态，定位空引用位置

4. 模拟Agent上下文调试 #

在VS中配置包运行时使用SQL Server Agent的服务账户，模拟生产环境上下文，尝试重现问题并调试。

解决建议 #

1. 修复脚本中的空引用风险
   对所有可能返回null的对象添加检查：

   • 访问变量前验证存在性与非空：

     If Not Dts.Variables.Contains("User::CurrentTrackingNo") OrElse Dts.Variables("User::CurrentTrackingNo").Value Is Nothing Then
         Throw New Exception("变量User::CurrentTrackingNo未设置或为空")
     End If
     

   • 访问XML节点前检查是否为null：

     Dim statusNode = WebResponse.SelectSingleNode("TrackingResponse/TrackingInfo/ConsignmentStatusDescriptive")
     Dts.Variables("User::ConsignmentStatus").Value = If(statusNode IsNot Nothing, statusNode.InnerText, "UNKNOWN")
     

2. 重新编译并部署包
   在VS2022中重新编译脚本任务，确保使用SQL Server 2022对应的.NET版本，然后重新部署包到SQL Server。

3. 清理SSIS运行时缓存
   删除路径C:\Program Files\Microsoft SQL Server\160\DTS\Temp下的临时文件，重启SQL Server Agent服务，清除缓存冲突。

4. 检查Agent账户权限
   确保SQL Server Agent服务账户具有访问目标Web服务、读取SSIS包变量的权限，必要时使用VS运行时的用户账户测试，排除权限问题。

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