0

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

enter image description here

user4912134
  • 1,003
  • 5
  • 18
  • 47
  • 1
    I'm surprised that compiles without error. Have you tried wrapping your asynchronous call to `executeInsert()` in a Task? Ref: [Can SSIS jobs be async?](https://stackoverflow.com/a/55568066/390122) – AlwaysLearning Apr 26 '22 at 04:04
  • @AlwaysLearning How would I wrap it.. Can you please give me an example.. please, I amnew to it could follow the one suggested in the article – user4912134 Apr 26 '22 at 04:10
  • I don't think SSIS allows for async programming. I am surprised you are getting as far as you are. – KeithL Apr 26 '22 at 17:09
  • @KeithL Do you recommend any solution with this issue? – user4912134 Apr 27 '22 at 02:26
  • @KeithL I need to load the API queryresponse in to the Azure SQL Database how can I approach it – user4912134 Apr 27 '22 at 02:27
  • try taking out all the async await stuff and step through it. You might want to make sure that the connection works as well. It looks like you are importing a ADO – KeithL Apr 27 '22 at 18:52
  • @user4912134 did you get any solution for the above one. Since I am also trying to load the data from API to SQL Server tables in VS2017. If you found the solution kindly suggest me. – venugopal May 23 '22 at 15:08

1 Answers1

0

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

felice.iorillo
  • 47
  • 6