0

I am calling a c# web API using SSIS script task. The requirement is to pick request data from a table, convert each row to a request, call API for each request and store response in a table.

I am looping around all rows of the table and calling API inside loop for one request (row) at a time. The entire process if i call API synchronously takes a lot of time. So i have started calling API Asynchronously using await and async.

The issue is that API is being called only a few number of times (mostly only 15-16 times). However the loop should call API total 100 times.

At the end of loop i am using whenAll to check if i have received responses for all the requests but it is not helping and still giving me only 15-17 responses every time. There is no failure anywhere.

If i use a sleep or if i wait for each task (which is same as calling API synchronously), API is being called for all 100 records.

I have already tried to wait each task

Task<MRMResponse> task =  obj.GetXMLObject(MRMEndPoint + filtercriteria,
        accessTokeninfo[0], requestData);

task.wait();

queryTasks.Add(task);

This helps and i can get API called for all 100 requests but this is not the way i know.

public async Task<List<MyResponse>> demoAsync(DataSet sourceDataTable, string[] accessTokeninfo, string connectionString, string DestTableName, string ClientId, string ClientSecret, string GrantType, string EndPoint, string MRMEndPoint, string OPSErrortableName, string BatchID)
    {
        List<MyResponse> finalResp = new List<MyResponse>();

        DataTable dtResponseOPS = GetDestTable(DestTableName, connectionString);

        // get Error Response Meta data Logging table MRM.OPSResponseMeta
        DataTable dtResponseMetaOPS = GetDestTable("MRM.OPSResponseMeta", connectionString);

        DBUtility utility = new DBUtility();

        List<Task<MyResponse>> queryTasks = new List<Task<MyResponse>>();

        foreach (DataRow rw in sourceDataTable.Tables[0].Rows)
        {
            MyResponse response = new MyResponse();

            ConsumeMRMAPI obj = new ConsumeMRMAPI();

            OPSRequestModel requestData = new OPSRequestModel();
            requestData = CreateItemFromRow<OPSRequestModel>(rw);
            string filtercriteria = "";

            reqCounter = IsTimeExpired();

            if (reqCounter > 65)
            {
                Thread.Sleep(Convert.ToInt32(tm.Interval));
            }

            if (DateTime.Now < tokenExpirytime)
            {
                Task<MyResponse> task =  obj.GetXMLObject(MRMEndPoint + filtercriteria, accessTokeninfo[0], requestData);

                queryTasks.Add(task);
            }
            else
            {
                accessTokeninfo = GetAccessToken(ClientId, ClientSecret, GrantType, EndPoint);
                tokenExpirytime = DateTime.Now.AddSeconds(Convert.ToInt32(accessTokeninfo[1]) - 300);

                //response =  obj.GetXMLObject(MRMEndPoint + filtercriteria, accessTokeninfo[0], requestData);

                if (!string.IsNullOrEmpty(response.validResponse))
                {
                    // destDataTable.Rows.Add(0, mpin, npi, taxId, fname, lname, null, null, null, null, response.validResponse, null, null, null, null, null, null, null, "1", BatchID);
                }
            }
            reqCounter++;

            if (dtResponseOPS.Rows.Count >= 100)
            {
                //parameters = SetupSPParameters(sourceDataTable,EnrichSystemID,BatchID);
                //resultCode = new DAHelper(connectionString).ExecuteSP(ProcedureName, parameters.ToArray(), out resultData, out errorCode, out errorNumber, out errorMessage);
                utility.DoInsertFileInfo(dtResponseOPS, DestTableName, connectionString);
                dtResponseOPS.Clear();
            }
        }

        await System.Threading.Tasks.Task.WhenAll(queryTasks);

        foreach (Task<MyResponse> task in queryTasks)
        {
            finalResp.Add(task.Result);
        }

        return finalResp;
    }
}
Peter Bons
  • 26,826
  • 4
  • 50
  • 74
gourav
  • 1
  • 1
  • 1
    Sidenote: you'd better replace `Thread.Sleep` with `Task.Delay` to make it non-blocking. – Peter Bons Oct 08 '19 at 18:27
  • You say not all requests are executed. Could it be that `if (DateTime.Now < tokenExpirytime)` evaluates to false so no request is created? – Peter Bons Oct 08 '19 at 18:28
  • There's a lot going on here, and much of it isn't shown, so it's difficult to help. (What is `ConsumeMRMAPI`? Why do you create a new one each time inside the loop? What happens inside `GetXMLObject`?) You would do well to reduce this to [a minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). Likely you would discover the problem yourself along the way, and if not then the result would be much more useful in asking for help. – Matt Johnson-Pint Oct 08 '19 at 18:37
  • @PeterBons - Thanks, will do that !! – gourav Oct 08 '19 at 19:17
  • @PeterBons - tokenexpiry is not the issue its set to fairly high value. Also these are just 100 requests shouldnt take more than a minute in synchronous and i am expecting even less in ASYNC calls. – gourav Oct 08 '19 at 19:18
  • @MattJohnson-Pint thanks Matt, i will try to minimize this. Also, Why do you create a new one each time inside the loop - can you explain that ? – gourav Oct 08 '19 at 19:26
  • The constructor, `ConsumeMRMAPI obj = new ConsumeMRMAPI();` is inside the `foreach` loop, so `obj` is a new instance of `ConsumeMRMAPI` for each data row. However, it appears on the surface that the only thing you do with it is call `GetXMLObject`, so it doesn't appear stateful. Likely you can move that constructor outside the loop and share a single instance. Of course, that depends greatly on the implementation... – Matt Johnson-Pint Oct 08 '19 at 19:33
  • Could be the case when some of called `GetXMLObject` calls ended in a *Faulted* state. You can check it if you get a `Task` object from `.WhenAll` method, await its completion and get then its state and exceptions. Try it. – Ferdipux Oct 10 '19 at 11:18

0 Answers0