C# script to load data into SQL Server database doesn't work as expected

Question

I am trying to query the APIs and insert the responses from it in to the SQL Server table using the script task. The script task is behaving in consistent that one time loads the data the other time the execution shows succeeded there is no data in the table.

Here is the C# code I am using to load the data:

public void Main()
 {
       // TODO: Add your code here
       executeInsert();
  }

 public async void executeInsert()
 {
   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;
}

}
Can anyone help me how this script shows succeeded no data loaded but other time it loads. I am kind of stuck any help is greatly appreciated

Answer 1

if (response.IsSuccessStatusCode)

Can anyone help me how this script shows succeeded no data loaded but other time it load

In your code you test if the response was successfull; but if not, nothing happens in the rest of your code, no error message or something else.