Long SSIS job failed with connection failure

  • I have a long ETL SSIS process, one step is to call a web service. The web service is slow for it process each person's record.

    Sometimes the job time out after run a couple of hours. with the below message:

    Status: CreateDriverDirectionsReport.ProcessRoutePath: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation

    Status: CreateDriverDirectionsReport: Connection failure

    Status: Handled exception in TDDReportJobThread.Execute: Connection failure: Level = B

    I have done all kinds of troubleshoot, like both webserver and the db server are online at the time it failed, and there is no error in SQL log, and web log, and I added RAM to the computer. but still cannot figure out.

    The same job runs fine on another server with exactly copy of databases.

    Today if failed again, then I restarted then after 8 hours(it is supposed to run that long, it is calling web service to process each route and stops, we only do this once a while.) , it succeeded.

    Does the IIS recycle time which is default 29 hours could affect the web service to interupt?

     

    Please advice.

    Thanks,

    • This topic was modified 1 month, 2 weeks ago by  sqlfriend.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I don't know how to fix your problem, but maybe this could be a work-around.

    Maybe you can split up the SSIS into more packages, then have the job call each package in succession.  The job step that runs the part that has been failing can have retries set, so that if it sometimes fails it might run again and succeed.  Of course you'd need to verify that the process is repeatable in case part of it works before the failure.

     

  • Thanks GaryV, that step is a step that calls a web service to process each route of each student. and in the web service I know it is truncate tables first, it is not add incrementally. So if it retries, I don't know if it is going to truncate table again or it just picks up in the place it get disconnected.

    If truncated, that is not what we want, for it takes a long time to reprocess again

  • The process failed intermittenly. For example on Monday It runs OK, on Tuesday it fails, on Thurday it runs OK again. Everytime it shows connection failure. Also at the same time I run on another server same job same database, no failure. So I don't think it is a network connection issue.

  • This was removed by the editor as SPAM

  • why there are some many spam from this site recently that sent to my email?

  • If someone spams a thread that you are getting replies, the spam reply will be sent to you.  Vigilance!

     

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply