Script task in SSIS package is executing but not performing the action

Question

I have the two SSIS packages which basically has two action like below

First it truncates the contents of the table and then it executes the script task like basically call an API and inserts the response in to the table

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {   
        public async void Main()
        {
            try
            {
                var sqlConn = new System.Data.SqlClient.SqlConnection();
                ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];

                string serviceUrl = Dts.Variables["$Project::RM_ServiceUrl"].Value.ToString();
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
                HttpClient client = new HttpClient();
                client.BaseAddress = new Uri(serviceUrl);
                client.DefaultRequestHeaders.Accept.Add(
                    new MediaTypeWithQualityHeaderValue("application/json"));
                string APIUrl = string.Format(serviceUrl + "/gonogo");
                var response = await client.GetAsync(APIUrl);
                if (response.IsSuccessStatusCode)
                {
                    var result = await response.Content.ReadAsStringAsync();
                    try
                    {
                        sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

                        const string query = @"INSERT INTO [dbo].[RM_Approved_Room_State]
                                          (APPROVED_ROOM_STATEID,SOURCE_ROOMID,DEST_ROOMID,ENTITY_TYPEID)
                                           SELECT id, sourceRoomRefId, destinationRoomRefId,entityRefId
                                           FROM OPENJSON(@json)
                                           WITH (
                                                 id int,
                                                 sourceRoomRefId int,
                                                 destinationRoomRefId int,
                                                 entityRefId int
                                                 ) j;";
                        using (var sqlCmd = new System.Data.SqlClient.SqlCommand(query, sqlConn))
                        {
                            sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result;
                            await sqlCmd.ExecuteNonQueryAsync();
                        }
                    }
                    catch (Exception ex)
                    {
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                    finally
                    {
                        if (sqlConn != null)
                            cm.ReleaseConnection(sqlConn);
                    }

                }
            }
            catch (Exception ex)
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion    
    } }

Similar to the above package I have another one which does more likely does insert records into different table the response from a different endpoint. When I execute the packages locally/ execute them separately after deploying it in to the server it works fine. But when I add them in to the SQL Server Agent Job like below and run them on a schedule

The Jobs run successfully and dont show any errors but I can see only one table with data from one package but the other one truncates the records but I dont think the script task is getting executed / I dont see any records inserted. I dont think there are any issues with access because when I run them seperate manually the data are getting inserted, Just when it is running on a schedule it is not working as expected. Any idea what could be happening here.. Any help is greatly appreciated