Click here to Skip to main content
14,333,745 members
Rate this:
Please Sign up or sign in to vote.
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);
            // 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)

                if (DateTime.Now < tokenExpirytime)

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


                    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);

                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);


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

            foreach (Task<MyResponse> task in queryTasks)

            return finalResp;

What I have tried:

I have already tried to wait each task

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



This helps and i can get API called for all 100 requests but this is not the way i know.
Richard Deeming 8-Oct-19 14:36pm
Not a solution, but if you can change the return type of the method to Task<IList<MyResponse>>, then you can replace that final loop with:
return await Task.WhenAll(queryTasks);

Task.WhenAll[^] returns an array of the results of the awaited tasks.
Richard Deeming 8-Oct-19 14:38pm
You mention you're calling this through SSIS - are you sure that it supports methods that return a Task? It sounds like it's not waiting for the task to finish.
gouravkaila91 8-Oct-19 16:24pm
I am not sure but SSIS script task uses C# 5.0 internally so that should be supported.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100