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.
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);
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);
if (!string.IsNullOrEmpty(response.validResponse))
{
}
}
reqCounter++;
if (dtResponseOPS.Rows.Count >= 100)
{
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;
}
}
What I have tried:
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.