August 18, 2021 at 12:21 am
Yes, dev and test servers hitting the same end point for the services, but the web services are hosted on two web servers, one is for dev web server, the other is for test web server. They have same VM resources like CPU, RAM, and they have installed the web service of same version .
The web service, is to get a list of route from vendors database and then output the data into another our own database, that is used for reporting. That database on dev and test are exact same version of copy too.
The web service process each route, find their stops, and students, and find the bus stop corner information and then insert data into our own database for reporting.
Vendors database doesnot store stop corner information, and even pull from UI the corner is populated on the fly. So we have to use their web service to get that.
August 18, 2021 at 12:43 am
Can you post a screen shot of the SSIS package?
It sounds like all it is an execute task that is calling some local web services and that web service is handling everything else, the calls to the vendor, loading the results into the database etc.....
August 18, 2021 at 1:19 am
Yes, the step is execute process task that has a executable C:\_SSISFILES\Miscellaneous\eLinkUtility\Transportation.ETL.VersaTransToStaging.exe
The executable call the web service that has endpoints.
August 18, 2021 at 4:48 am
unfortunately it is not something simple that T-SQL can handle. There are complicated logic to calculate stop corners according to time and direction of the bus etc, that is why the vendor created the web service API. And we don't know their logic of how they do it.
It's funny that people say that T-SQL can't handle something when they don't know what the logic of how a 3rd party does it actually is. 😉 If I were the 3rd party, that's what I'd tell people so I could keep the business. 😉
Anyway, I have a full appreciation of what you're going through there. I just think that you're actually spending time on the wrong thing. The difference between 8 hours and 10 hours can be summarized as "sucks alot" and "sucks more than alot". I'd be riding herd on the 3rd party vendor to fix things. I'd also serious be looking for alternatives.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2021 at 11:52 am
Yes, the step is execute process task that has a executable C:\_SSISFILES\Miscellaneous\eLinkUtility\Transportation.ETL.VersaTransToStaging.exe
The executable call the web service that has endpoints.
I'm guessing that snippet of C# code is from the executable you're calling?
You're probably going to have to unravel that whole mess, what that executable is actually doing, what the local web service it's calling is doing and what the vendor services are doing. Then isolate each component, specifically the calls from your local web servers to the vendor, the db writes and whatever processing is being done(you'll have to figure out whether that's in the executable or in your local webservice).
Try starting with whoever is responsible for the local web service you're calling, figure out what calls it's making to the vendor and get timings on those. Also verify if those are hitting the same endpoint on the vendor side or if the vendor also has dev/test/prod systems. It sounds like the vendor has multiple services, getting the route then calling some other services based on that, you should be able to break that down and get a timing for a single call of each one.
As for the executable, you said it gets a list of routes? Where does it get that from? Can it be run in parallel? That will come down to understanding what it's actually doing.
August 18, 2021 at 3:28 pm
Thanks for your recommendations. What I can say is the .exe is calling our local web application that calls vendor's web service end points. Vendor side web service is the same, no dev, test environment, all the same. We are at a turnover time of .net developers, but I will try to find another one to see if he has ideas. As a DBA, I have little experience of.net code side. With that being said I posted a piece of code earlier, I wonder if that thread.sleep(1) makes the whole time of the job real long. while (_vtStudentService.GetJobStatus(jobId) == VT.StudentService.JobStatusType.jsPending)
{// wait...
Thread.Sleep(1);}
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply