SSIS Package Produces Second Error Upon Failure When Ran From SQL

Question

OS - Windows Server 2019 Build 17763
SQL Version - 2019 Standard (15.0.4073.23)
Visual Studio Professional 2019 and VSTA 2019

I have an SSIS package that contains only a script task which calls a web API depending on parameters, and error handling that calls a stored procedure to send emails upon the script task failing.

If I run the package manually from Visual Studio and force the script to fail (e.g. I give it bad data which the API sends an error response to, I capture and fail the task) I get only one error and one email with the details of that error.

If I run the package from a SQL stored procedure, with the same parameters, I end up with 2 errors (and 2 emails). One email being the expected error message, and the second being a Microsoft error which I will paste here:

The Execute method on the task returned error code 0x80070002 (Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.). The Execute method must succeed, and indicate the result using an "out" parameter.

From my analysis, it seems as if the application is trying to open Visual Studio Tools for Applications (VSTA) 2017 upon a failure, and not finding it because we have the 2019 version installed. Researching the error code just turns up results talking about 64-bit vs 32-bit, but no changes to those settings or installing of different versions have made any different result.

Any advice appreciated, thank you.

UPDATE: Additional Information Below

SSIS Package Parameters:

Script task parameters:

Here is the bulk of the script task code, there's a bit more like the dictionary class code but it shouldn't be relevant. Also it should be noted that I use a DataSource (.xsd) to connect to the database (denoted in the code as PISData):

   // this function takes an integer and uses it to select an endpoint record from the database  
           private string setEndpoint(int integrationID)  
           {  
               try  
               {  
                   PISData.ProgramIntegrationSpecificationsDataTable manDBData = new PISData.ProgramIntegrationSpecificationsDataTable();  
                   PISDataTableAdapters.ProgramIntegrationSpecificationsTableAdapter manDBTableAdapter = new PISDataTableAdapters.ProgramIntegrationSpecificationsTableAdapter();  
                   manDBTableAdapter.FillByIntegrationID(manDBData, (short)integrationID);  
                   int rw = Int32.Parse(manDBData.Rows[0][2].ToString());  
                   write = (rw != 1);  
                   string endpoint = manDBData.Rows[0][7].ToString();  
                   return endpoint;  
               }  
               catch (Exception ex)  
               {  
                   Dts.TaskResult = (int)ScriptResults.Failure;  
                   Dts.Events.FireError(0, "Error caught while setting endpoint: ", ex.Message, String.Empty, 0);  
                   return null;  
               }  
           }  
     
           // retrieves data from API endpoint, will also accept additional data to postfix on the endpoint  
           private string getData(string endpoint, string data = null)  
           {  
               string response;  
               try  
               {  
                   using (WebClient client = new WebClient())  
                   {  
                       client.BaseAddress = endpoint;  
                       client.Headers.Add("accept", "*/*");  
                       client.Headers.Add("Authorization", "Basic REMOVED");  
                       response = client.DownloadString("");  
                       return response;  
                   }  
               }  
               catch (WebException ex) when (ex is WebException)  
               {  
                   HttpWebResponse wr = (System.Net.HttpWebResponse)ex.Response;  
                   responseCode = wr.StatusCode;  
                   Stream dataStream = wr.GetResponseStream();  
                   StreamReader reader = new StreamReader(dataStream);  
                   response = reader.ReadToEnd();  
                   return response;  
               }  
     
               catch (Exception ex) when (!(ex is WebException))  
               {  
                   Dts.TaskResult = (int)ScriptResults.Failure;  
                   Dts.Events.FireError(0, "Error caught while retrieving data: ", ex.Message, String.Empty, 0);  
                   return null;  
               }  
     
           }  
     
           // sends JSON data to endpoint and returns result  
           private string postData(string endpoint, string data)  
           {  
               string response = "";  
               using (WebClient client = new WebClient())  
               {  
                   try  
                   {  
     
                       client.BaseAddress = endpoint;  
                       client.Headers.Add("accept", "*/*");  
                       client.Headers[HttpRequestHeader.ContentType] = "application/json";  
                       client.Headers.Add("Authorization", "Basic REMOVED");  
                       response = client.UploadString("", data);  
                       return response;  
                   }  
                   catch (WebException ex) when (ex is WebException)  
                   {  
                       HttpWebResponse wr = (System.Net.HttpWebResponse)ex.Response;  
                       responseCode = wr.StatusCode;  
                       Stream dataStream = wr.GetResponseStream();  
                       StreamReader reader = new StreamReader(dataStream);  
                       response = reader.ReadToEnd();  
                       return response;  
                   }  
     
                   catch (Exception ex) when (!(ex is WebException))  
                   {  
                       Dts.TaskResult = (int)ScriptResults.Failure;  
                       Dts.Events.FireError(0, "Error caught while posting data: ", ex.Message, String.Empty, 0);  
                       return null;  
                   }  
               }  
           }  
     
           // writes result to database  
           private void writeResultToDB(long exID, string response)  
           {  
               try  
               {  
                   PISData.APIResponseInformationDataTable apiData = new PISData.APIResponseInformationDataTable();  
                   PISDataTableAdapters.APIResponseInformationTableAdapter apiDBTableAdapter = new PISDataTableAdapters.APIResponseInformationTableAdapter();  
     
                   string transactionID = "";  
                   string responseMessage = "";  
                   int end;  
                   Dictionary<string, object> responseDict;  
     
                   if (write || responseCode != HttpStatusCode.Accepted)  
                   {  
                       responseDict = ParseJSON(response, 0, out end);  
                       if (responseDict.ContainsKey("transactionId"))  
                       {  
                           transactionID = responseDict["transactionId"].ToString();  
                       }  
                       if (responseDict.ContainsKey("jdeReturnMessage"))  
                       {  
                           responseMessage = responseDict["jdeReturnMessage"].ToString();  
                       }  
                   }  
     
                   apiDBTableAdapter.Insert(exID, responseCode.ToString(), transactionID, responseMessage, response);  
               }  
               catch (Exception ex)  
               {  
                   Dts.TaskResult = (int)ScriptResults.Failure;  
                   Dts.Events.FireError(0, "", "Error caught while writing result to DB: "+ ex.Message, String.Empty, 0);  
               }  
           }  
     
           /// <summary>  
           /// This method is called when this script task executes in the control flow.  
           /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
           /// To open Help, press F1.  
           /// </summary>  
           public void Main()  
           {  
               int integrationID = (int)Dts.Variables["$Package::endpointId"].Value;  
               string data = Dts.Variables["$Package::data"].Value.ToString();  
               string result;  
               bool fireAgain = false;  
               Microsoft.SqlServer.Dts.Runtime.Variables lockedVariables = null;  
     
               Dts.Events.FireInformation(1, "PARAMS", "SCRIPT START; PARAMS: integrationID = " + integrationID.ToString() + " data: " + data, "", 0, ref fireAgain);  
     
               string endpoint = setEndpoint(integrationID);  
     
               Dts.Events.FireInformation(1, "ENDPOINT SET", "ENDPOINT HAS BEEN SET", "", 0, ref fireAgain);  
     
               result = write ? postData(endpoint, data) : getData(endpoint, data);  
     
               Dts.Events.FireInformation(1, "RESULT: ", "REQUEST STATUS CODE: " + responseCode + "REQUEST RESULT: " + result, "", 0, ref fireAgain);  
     
               if (result == null) return;  
     
               long exID = Int64.Parse(Dts.Variables["System::ServerExecutionID"].Value.ToString());  
     
               writeResultToDB(exID, result);  
               Dts.Events.FireInformation(1, "", "WRITE TO DB FINISHED", "", 0, ref fireAgain);  
     
               if (responseCode != HttpStatusCode.Accepted)  
               {  
                   Dts.VariableDispenser.LockOneForWrite("User::ErrorCode", ref lockedVariables);  
                   lockedVariables["User::ErrorCode"].Value = 1;  
                   lockedVariables.Unlock();  
                   Dts.Events.FireError(0, "EXECUTION ID: " + exID.ToString() + "ERROR RESPONSE", "JDE Response Code is in Error: " + (int)responseCode + " - " + responseCode + "\n\n FULL RESPONSE: " + result + "\n\n CALLED WITH DATA: " + data, String.Empty, 0);  
               }  
               else  
                   Dts.TaskResult = (int)ScriptResults.Success;  
           }  
     
           #region ScriptResults declaration  
           /// <summary>  
           /// This enum provides a convenient shorthand within the scope of this class for setting the  
           /// result of the script.  
           ///   
           /// This code was generated automatically.  
           /// </summary>  
           enum ScriptResults  
           {  
               Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,  
               Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure  
           };  
           #endregion  
     
   }  
   }  

Here is an example of the execution TSQL code, which we use in several stored procedures:

--SSIS Package Definitions and Execution  

Declare @execution_id bigint  
Declare @ssis_status int  
Declare @ssis_error_msg nvarchar(max)  

-- create execution and get execution ID  
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',  
	@execution_id=@execution_id OUTPUT,  
	@folder_name=N'JDE',  
	  @project_name=N'JDE API Caller',  
	@use32bitruntime=False,    
	  @reference_id=Null  
Select @execution_id  

-- set execution parameters  
	-- set endpoint to put pipe on hold or take pipe off hold depending on request.  
IF (@HoldWW = 1)  
	BEGIN  
  	EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'endpointId', @parameter_value=1  
	END  
ELSE  
	BEGIN  
 	EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'endpointId', @parameter_value=3  
	END	  


DECLARE @data nvarchar(3000) = N'{   "lotNumber": "' + @PLC_LotNo + N'", "reasonCode": "' + CAST(@HoldReason AS nvarchar) +N'"}' -- concatenates lot number to JSON format  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'data', @parameter_value=@data  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1  
DECLARE @var0 smallint = 1  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
	@object_type=50,  
	  @parameter_name=N'LOGGING_LEVEL',  
	  @parameter_value=@var0  

-- execute package  
EXEC [SSISDB].[catalog].[start_execution] @execution_id  

-- get status, if error get error message  
SELECT @ssis_status = [status]  
  FROM [SSISDB].[catalog].[executions]  
  WHERE [execution_id] = @execution_id  

  IF (@ssis_status <> 7 and @ssis_status <> 9)  
	BEGIN  
	SELECT TOP(1) @ssis_error_msg = [message]  
	  FROM [SSISDB].[catalog].[operation_messages]  
	  WHERE [operation_id] = @execution_id AND [message_type] = 120;  
	  declare @Message varchar (500)  
	  SET @Message = 'Update JDE with PIPE Hold Information Failed for pipe:  '   
      SET @MESSAGE = @MESSAGE + @PLC_LotNo + CHAR(13) + '  Execution ID: ' + @execution_id + char(13)  
	  set @Message = @Message + 'SSID Error Msg: ' + @ssis_error_msg  
      EXEC msdb.dbo.sp_send_dbmail   
      @profile_name = 'WW System',  
      @recipients='******@american-usa.com; ******@american-usa.com;',   -- QA after testing  
      @body = @message,   
      @subject ='Update JDE with PIPE Hold Information Failed'   
	END  

Answer 1

I see that your Main() method has one questionable explicit exit point:

Dts.TaskResult = (int)ScriptResults.Success;

I would suggest the following approach. It will use a new rc (return code) boolean variable.
The Main() method will have one single exit point based on the rc value for both cases, i.e. positive and negative.

c#

void Main()
{
   bool rc = true;

   try
   {
      if (whatever logic)
      {
         ...
      }
      else
      {
         rc = false;
         // Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
         Dts.Events.FireWarning(1, Dts.Variables["System::TaskName"].Value.ToString()
              , string.Format("File '{0}' does not exist", fileInfo.FullName)
              , "", 0);
      }

   }
   catch (Exception ex)
   {
      rc = false;
      // Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
      Dts.Events.FireError(18, Dts.Variables["System::TaskName"].Value.ToString()
           , ex.Message.ToString()
           , "", 0);
   }

 Dts.TaskResult = (rc) ? (int)ScriptResults.Success : (int)ScriptResults.Failure;
}

Answer 2

Hi @Nelson, Andrew ,

Please check if you have downloaded the newest SQL Server Integration Services.

Please refer to the following links:

Install Integration Services (SSIS)

Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications.Core, Version=10.0.0.0

Best Regards,
Mona