David,

This article is extremely helpful. Great job and thank you for submitting!

I am running into a problem connecting to some of my servers, not all. I have compared between the environments and cannot find a difference. Can you tell me what all should be open on the server side in order for this connection to be successful? From my troubleshooting, I have found that I had to enable OPENROWSET advanced option on the remote server. I thought this would fix the problem, but apparently there is something else blocking it. I also ensured that named pipes was enabled. I am running this from my machine and can connect to all servers in my ServerList table, so I don't think security is an issue.

Any help would be greatly appreciated. I have been working on this for hours!

The error populating the Errors table is:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".".