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

dhana vellachamy 1 Reputation point
2022-04-26T10:20:12.83+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,522 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,630 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2022-04-28T07:43:33.067+00:00
    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.

    0 comments No comments