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

Seems you have async calls inside your method, so it should be a Task

Try this way:

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

    public async Task executeInsert()
    {
      ....

The calling method should also be async in order to await the result. In this case I guess you are doing a simple test on a console application