SSIS Error: Named Pipes Provider: The specified network name is no longer available.

A perfectly working package suddenly throws an error;

“[Lookup for - Pending Duration [7825]] Error: 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: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Named Pipes Provider: The specified network name is no longer available.
".”

This package is a kind of complex one, it contains three Data Flow tasks and couple of other tasks. It basically loads a Fact Table in one of Data Warehouses, goes through set of LookUps which have set for Dimension tables.

The error raises with one of the LookUps. The lookUp is supposed to go through around 40,000 records but stops at around 6,000, throwing the error. Once this started giving the issue, the only difference we can thought about current execution and early executions is, the amount of data, it has been increased.

Solving the issue by changing the protocol TCPIP
After searching for solutions, going through many posts, based on some recommendations, we decided to force SSIS to use TCPIP instead of Named Pipes. We disabled the Named Pipe protocol in SQL Server through SQL Server Configuration Manager. We did the same with where we run the package. Finally we ran the package and ended up with same point;

“[Lookup for - Pending Duration [7825]] Error: 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: "TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted.
".”

We realized that the issue is not exactly related to protocols used by the package hence, went through some of the implementations we have done where we have slightly derailed the track of standardization. One implementation was related to transactions, where we had used explicit BEGIN TRAN and COMMIT TRAN to control the transactions without using the built-in facility. This type of implementation has been explained here if you need to know how to do it. The reasons for using this is arguable but some of them were, MS DTC issues, Firewalls, and company policies.

In order to set the transaction like above, we need to set the RetailSameConnection as True in OLE DB connection. That was the culprit. Since this forces SSIS to maintain one connection through out the package execution, it seems that using one connection for all lookups is not efficient.

Solving the issue
One easiest way to solve the issue is, adding Timeout=0 to the connection string. But this slows down the package execution vastly, though it succeeds. The next way is, handling the transactions in different way. We thought to use the second way because the time takes for the first one is not acceptable.

The above mentioned errors may occur for some other reasons too but this could be the one if you have already face :).