locked
Communication link failure when connecting to remote database RRS feed

  • Question

  • Hi,

     

    I have an SSIS package which runs subsequent packages in a batch.  When I run the main package through command line or a SQL job and connect to a data warehouse that is on the same machine, it runs without a problem.  When I try and connect to a warehouse on a remote machine (ideal configuration), it fails with this error:

     

    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 Native Client"  Hresult: 0x80004005  Description: "Communication link failure". 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: "TCP Provider: An existing connection was forcibly closed by the remote host. ".

     

    This seems to happen on random subpackages and isn't always consistent. 

     

    My Master.dts.Config file has this connection info:

     

    <Configuration ConfiguredType="Property" Path="\Package.Connections[Batch].Properties[ConnectionString]" ValueType="String">
      <ConfiguredValue>Data Source=RemoteServerName;Initial Catalog=DataWarehouse;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
     </Configuration>

     

    Both machines have 64-bit operating system and 64-bit SQL software.

     

    Any help would be great.

     

    Thanks.

    Monday, January 28, 2008 9:32 PM

Answers

  •  

    Do you have the same problem when using SQLOLEDB.1 rather than SQLNCLI.1

    Since you say it is random and inconsistent, it looks like the server accepts connections.

    Tuesday, January 29, 2008 12:55 AM

All replies

  • Does your remote server allow remote connections?  Can you connect to that remote server with SQL Management Studio?
    Tuesday, January 29, 2008 12:04 AM
  •  

    Do you have the same problem when using SQLOLEDB.1 rather than SQLNCLI.1

    Since you say it is random and inconsistent, it looks like the server accepts connections.

    Tuesday, January 29, 2008 12:55 AM
  • Thanks so much for the help.  When I changed the provider to SQLOLEDB.1, it ran without a problem.

     

    Thanks again!

    Tuesday, January 29, 2008 3:13 PM
  • Phillipe that's brilliant - I'm very grateful for your contribution.

     

    For others who end up here, I obtained the following set of errors:

     

    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: "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." (although it wasn't this!!)

    ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.

    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure".

    SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

    The AcquireConnection method call to the connection manager "xxxx" failed

    with error code 0xC0202009.

    There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

     

    ...in particular, scouring through the army of results returned by searching on DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, brought me no joy.

     

    Hope this helps someone save time!

    Monday, February 4, 2008 4:15 PM
  • Thanks for the vote of confidence.

    My answer is merely a workaround for a defective data provider.

    I had little luck with "SQL Server destination" in SSIS, it is very unstable.

    I just do not have a way to submit a bug report that would be reproducible and correctly documented. It is somewhat an intermittent issue.

    The fact is that this data provider is bogus. It looks like that problems happen more easily when you deploy the package to another server or run the package through SQL Server Agent.

    Monday, February 4, 2008 4:30 PM
  • I am getting the same error. And the workaround to use SQLOLEDB.1 in place of SQLNCLI.1 does not work for me.
    Thursday, April 19, 2012 11:14 AM
  • In SSIS 2012 I had such an issue crop up unexpectedly.  To rid myself of the error I set the RetainSameConnection property on the connection.  I hope this helps.
    Tuesday, June 19, 2012 1:34 AM
  • Thanks so much,
    I was really very useful.
    Great and good job.
    teoJam

    Tuesday, December 4, 2012 11:35 AM
  • Hi Phil,

    yes, I am using remote server with SQL Management Studio for this connection. 

    Wednesday, August 14, 2013 11:25 AM
  • Hi Michael,

    I am using OLEDB Source and OLEDB Destination.

    I am seeing occasional and seemingly random network/connection related errors for both - similar to those above.

    Do you recall if you set RetainSameConnection=true for your source and destination connections?

    My source is a remote SQL Server database.

    My destination is a local SQL Server database.  This database is on the same server that's hosting SSIS service, the package stored on the file system, and the job calling it.

    I know this post is over a year old - but maybe you've subscribed to this thread.

    Thanks!

    Tom

    Wednesday, September 11, 2013 10:15 PM
  • Tom, did you find an answer ?.....my source is a remote 2005 SQL Server database view, my destination is a SQL Server 2008 R2 table..sometimes the data flow works, sometimes not.

    It's driving me nuts, trying to figure out why it sometimes works and not others.....

    Any help would be greatly appreciated !!

    Kim

    Monday, September 30, 2013 6:56 PM
  • We also have been experiencing this "Communication Link Failure" randomly. 

    At least one cause we have found is our enterprise backup solution.

    (however this does NOT explain all the "Comm Link Failures" we have seen).

    Our Enterprise backup solution requests VMware to create a Quiesce=true Snapshot

    (in our backup software this is called an "application consistent" backup)

    We have seen when the VMware snapshot is created and when it is finally later "committed"

    we can get "Communication Link Failures".

    So we have learned to move our SSIS packages to not run when the Backups run.

    ==============

    I look forward to trying some of the other solutions mentioned in this article to see if they help our remaining "comm link failures"

    -Gary 

    Wednesday, May 14, 2014 3:03 PM