Web service with SQL server lost connection

Question

I have a complicated SSIS package with child packages, and it scheduled as a SQL agent ETL job.
One of the steps is to call a .net application that calls a web service.
The web service is hosted on a web server, then it queries the database on the server where the ETL job runs.

We run the job once a week, , it is a process that runs over 6-8 hours.

It failed intermittently some times when it is over 6 hours with lost database connection error.
If I resume the job manually, it continues without error, so it is not data problem.

in windows even viewer I see this.
Status: CreateDriverDirectionsReport.ProcessRoutePath: [DBNETLIB][ConnectionWrite (send()).]

I have tried my best to troubleshoot it, still keeps mystery. It succeeded most of time. but some time it fails. The database is online, memory of SQL server is good.
Any other idea how I can do troubleshoot?

Thanks

Answer 1

Hi

Have you tried enabling SSIS logging to see more details?

integration-services-ssis-logging

The error message is a bit hard for us to narrow down the issue.

You can check SQL Server Agent’s activity logs and SSIS logs to get more clues.

Also the tool Process Monitor is helpful to track the cause of the issue.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October