SQL Server connection timeout issue

We have a SQL job scheduled a SSIS package each day, it reads data from a remote SQL Server, across AP region to AM region.
Sometimes it executes successfully, but sometimes it fails after executing for about 20 seconds (refer to attached screen shot).

The error message is same each time:

Hresult: 0x80004005  Description: "Login timeout expired".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unable to complete login process due to delay in prelogin response".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: Timeout error [258]. "


Does anyone know what might be caused such error? We looked the sql logs on destination SQL Server, and there is nothing relavant logged when job executes. Both source and destination SQL Server timeout setting is 10 minutes. SSIS package has timeout setting as 0 which is never timeout.

Thanks a lot!
job-history-screenshot.png
xuewei shiDBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Goodangel MatopeSoftware ArchitectCommented:
This is likely a network related issue, if you are only getting the failure sometimes. If you were getting the error all the time, it would possibly be an SSIS version issue where you need to get a newer version of SSIS. However since it works sometimes, then likely you have a slightly wonky network. Have your networking guys have a look at the network. I had some similar problem a while back and it was network issues, despite the fact I was running SSIS on the same server. Weird, but well, that's my experience.

Just an aside, don't you think it might be safe to have some sort of timeout value in your package? because never timeout may cause locks which will prevent other processes / queries from running and hence cause logs to balloon, and cause running queries not to commit while waiting for this process to finish?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Talk with the network guys. I can see that it's happening earlier in the morning so must be network congestion. Backups running at the same time, maybe?
xuewei shiDBAAuthor Commented:
Thanks guys for your kindly help. I am sorry for late reply and response of my this question,
Yes it was due to the network problem and network team has solved that already.
Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.