Hi, I have a SSIS running on DB1 . A Loop inside this SSIS has one execute sql task connect to another server DB2 using local IP. This sql task insert data to DB2. I am running a profiler on DB2 to trace the INSERT statement. I found out that the loop is working fine because few data is inserted successfully but the job is failed intermittently. I got the error message in detail but couldnt find the solution. Executed as user: DEV\DevSQLServerDE. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:37:58 AM Error: 2011-03-11 09:38:26.74 Code: 0xC0202009 Source: TransferClientV3_Import Connection manager "MainDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" 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.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". End Error Error: 2011-03-11 09:38:26.74 Code: 0xC00291EC Source: Insert RecordCount History Execute SQL Task Description: Failed to acquire connection "MainDB". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:37:58 AM Finished: 9:38:26 AM Elapsed: 28.127 seconds. The package execution failed. The step failed. Thanks, Kenny

Ping is not a cure but rather a band - aid like quick peek into whether you have basic connectivity. Besides, you did not ping long enough. You can either do the Ping using the -n switch for a larger number of attempts e.g. Ping serverABC -n 900 or put a Ping into a batch to execute multiple times at an interval of say 10 min and redirect the Ping to a file to examine for time outs. Now apart from this basic connectivity test a Firewall can wreck your attempt to establish a connection with a database. I mean that for example a certain load balance setting or routing is in effect that directs the traffic at times elsewhere. To troubleshoot this you can use a UDL file to check if it can connect successfully multiple times. To create the file create a new file of any kind and rename its extension to .udl. It will become a file that is similar to a database connection tool. Then use it to connect to your target from the troubled machine several times (you can even run your package and try to connect at the same time), if both die but the Ping works you have a Firewall or other (e.g. DNS) settings issues.Arthur My Blog

Because it is intermittent in nature you need to rather investigate the network stability.Arthur My Blog

I concur with Arthur - this is a network reliability issue or resources issue on the server. It may look fine "now" when you ping, but there is definitely a period when it isn't up. Is there a pattern to the failures? Are there other jobs executing? Are there other processes consuming resources at these times? Talk to me now on

The network looks good to me. Reply from 10.20.0.171: bytes=32 time<1ms TTL=128 Reply from 10.20.0.171: bytes=32 time<1ms TTL=128 Kenny

Might be also the driver, is there a patch or an upgrade to it?Arthur My Blog

I have reinstalled Microsoft® SQL Server® 2008 R2 Native Client but still getting the same error. This is a new machine, no other processes consuming resources. I am not a network guy but what I know is as long as the ping doesn't show timeout means the network is good.