SSIS Script Task "Object reference not set to an instance of an object" When Executed from SQL Server Agent Job

Question

I have a strange issue. I have an SSIS package that runs on a SQL Server Agent job. It intermittently fails with the error "Object reference not set to an instance of an object" and I can see that the source is the script task. It then fails on every subsequent run until I open the package in Visual Studio (2022 and on the same machine) and run it from there. It normally succeeds and then it also succeeds on the job until the next time it fails. Sometimes a server reboot fixes the issue but not always.

Some other info:

• The package was upgraded from 2014 (VS2013 data tools)
• The Visual Studio project uses the package deployment model
• The SQL Server database version is 2022

Any idea what's going on? Is there a way of "debugging" the script task whilst it's running on the job?

As I said I am able to resolve the problem by manually running the package in Visual Studio but I need it to run on a job as it runs every hour.

Code below:

   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