SSIS SQL Native Client Error - Can't Diagnose Cause

Question

We have a very large number of SSIS jobs that are scheduled every evening/early morning to run in succession. These jobs populate and update large amounts of data for our production systems. Recently, we have begun receiving an error message on different jobs at different times. So far, it has been impossible to reproduce on a consistent basis:

Code: 0xC0202009     Source: [Job Name] Connection manager "[Connection.Manager.Name]"     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 Native Client"  Hresult: 0x80004005  Description: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Named Pipes Provider: No process is on the other end of the pipe.  ".  End Error  Error: 2009-03-10 05:19:51.09     Code: 0xC00291EC     Source: Update record status Execute SQL Task     Description: Failed to acquire connection "[Connection.Manager.Name]". Connection may not be configured correctly or you may not have the right permissions on this connect...  The package execution fa...  The step failed.

The connection is definitely configured properly, and we are running it as a user with the proper permissions. For over a year, these jobs had performed flawlessly. Google searches turn up results that seem to span everything from possible connection issues to data integrity issues. We have tried working this from the data source end as a connectivity problem, and from the SQL Server db and the server box by checking event logs. Nothing seems to line up. Here is our setup:

• We have one Server 2003 box with SQL Server 2005 dedicated to just housing and running SSIS jobs
• We have a dedicated Server 2003 box with just the SQL Server database on it that houses our data and also serves up Reporting Services reports
• Most of our jobs connect via ODBC to a Sybase DB to get data from our system of record and bring it down to the SQL Server for reporting and data manipulation

Has anyone run across this exception in a similar manner? Again, we have tried to troubleshoot the SQL Server DBs and also the Sybase connection with no luck.

Answer 1

If OLE DB is lying to you, then this could be anything at all. If it's not, then maybe there's no process on the other end of the pipe. You should look in the event logs, etc. to see if maybe the other process died.

Even processes that have been running in production for years can change their behavior if something else changes. For example, a backup or other automated job may be taking longer and longer, and now collides with the existing schedule of your SSIS packages. Such a thing might force closed any open connections, causing "no process on the other end of the pipe".

Answer 2

I am getting the same issue with one of our job - were you able to resolve it ? if yes - can you share some info what I should be looking for. Thanks in advance.

Answer 3

In our case it was resource contention. Deadlocks were occurring due to table level locks from other jobs. The Native Client error wasn't descriptive enough to let us know what was wrong initially. We had to run SQL Server Profiler on the DB and filter for Deadlock errors. This article helped a lot to get us pointed in the right direction.

Hope that helps!!