SSIS Connection Timeout Errors

Question

I have a SSIS package which connects to a list of  5-8 SQL servers and downloads data into central location.

The package in giving me intermittent connection timeouts which connecting to the source. This error happens randomly on any of the 5-8 servers from where data needs to be pulled.

I am developing on SQL Server 2008 Developer edition which is installed on Windows Web Server 2008 R2 64-bit.

I have defined a dynamic OLEDB connection which uses SQLNCLI10.1 provider.

Below is the error I am getting

Hresult: 0x80004005  Description: "Login timeout expired".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

Error: 0xC020801C at 'XXX Data Flow Task', XXX Server [7169]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ABC-CONNECTION" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Has anyone faced a similar issue?

---

Best Regards, Nimesh http://www.prosqlserver.com/blog Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 1

I have. Unfortunately, you have almost no options in eliminating the errors pertaining to the connectivity because they typically reside within the network layer.

Therefore I suggest turning to whoever is in charge for your network and ask to troubleshoot the latency in establishing a remote connection.

---

Arthur My Blog

Answer 2

In order to troubleshoot, you could try and increase the value of "Connect Timeout" property of each of the SOurce connection managers if it set to something non-default. However, if the issue is with your network dropping the connection, you'll be better served if you get it investigated by your IT Operations team.

Hope this helps!

Cheers!!

Muqadder.