question

dhanavellachamy-3836 avatar image
0 Votes"
dhanavellachamy-3836 asked OlafHelper-2800 answered

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

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

dotnet-csharpsql-server-integration-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@dhanavellachamy-3836, Welcome to Microsoft Q&A, Could you tell me the json result you get like the following?

    var result = await response.Content.ReadAsStringAsync();
0 Votes 0 ·

Even though I added breakpoint on the line it is not hitting the line but exists right after the var response = await client.GetAsync(APIUrl); line but shows package has successfully completed.

0 Votes 0 ·
JackJJun-MSFT avatar image JackJJun-MSFT dhanavellachamy-3836 ·

@dhanavellachamy-3836, you could write the result to the file or output it directly like the following:

 Console.WriteLine(result);
    
 File.WriteAllText("","")


0 Votes 0 ·

This may or may not help but do not use async void for a method, instead use async Task and change the signature of Main along with adding await to the method call.

 class Program
 {
     static async Task Main()
     {
         await ExecuteInsertAsync();
     }
    
    
     public static async Task ExecuteInsertAsync()
     {
         ...
     }
 }


0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered
 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.