What might be causing this error message in the execution of my SSIS package as a SQL job?

0

I am using SQL Server 2014 and I have a SQL job scheduled to run some SSIS packages on a daily basis at a specific time. The SQL job has got 10 steps (10 SSIS packages) in it. The job copies tables from a linked server into the database. My problem is that the job fails on average, around twice a week. It does not fail on the same step each time but there seem to be a pattern of failure on large tables. One such failure occurred today and the error log is shown below:

    09/03/2018 08:38:58,HMSToPrestaging,Error,9,BI-SQL\AHMLBI,HMSToPrestaging,
09 ReservationStayDate Table,,Executed as user: XXXXXX\sql_bisvcs.
 Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  
Copyright (C) Microsoft Corporation. All rights reserved.
 Started:  8:38:58 AM  Error: 2018-09-03 08:50:11.87
Code: 0x00000000     Source: Execute SQL Task
Description: OLE DB provider "SQLNCLI11" for linked server "xxx.xx.x.xx" returned message 
"Protocol error in TDS stream".  End Error  Error: 2018-09-03 08:50:11.87  
Code: 0x00000000     Source: Execute SQL Task 

Description: OLE DB provider "SQLNCLI11" for linked server "xxx.xx.x.xx" returned message 
"Communication link failure".  End Error  Error: 2018-09-03 08:50:11.87     
Code: 0x00000000     Source: Execute SQL Task 
Description: Session Provider: Physical connection is not usable [xFFFFFFFF].
End Error  Error: 2018-09-03 08:50:11.87 
Code: 0x00000000     Source: Execute SQL Task      
Description: OLE DB provider "SQLNCLI11" for linked server "xxx.xx.x.xx" 
returned message "Communication link failure".  
End Error  Error: 2018-09-03 08:50:11.87 Code: 0x00000000
Source: Execute SQL Task 

Description: Session Provider: Physical connection is not usable [xFFFFFFFF].   
End Error  Error: 2018-09-03 08:50:11.87     Code: 0x00000000     
Source: Execute SQL Task      
Description: OLE DB provider "SQLNCLI11" for linked server "xxx.xx.x.xx" returned message 
"Communication link failure".  End Error  Error: 2018-09-03 08:50:11.87 
 Code: 0xC002F210    
 Source: Execute SQL Task Execute SQL Task

Description: Executing the query "DELETE FROM Prestaging.dbo.[ReservationSta..." 
failed with the following error:
 "TCP Provider: An existing connection was forcibly closed by the remote host. ".

Possible failure reasons: Problems with the query<c/> 
"ResultSet" property not set correctly<c/>
 parameters not set correctly<c/> or connection not established correctly.  
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  
Started:  8:38:58 AM  Finished: 8:50:11 AM  Elapsed:  673.11 seconds.
  The package execution failed.  The step failed.,00:11:13,0,0,,,,0

What may be causing this error?

I have been looking at the following resources (but I am still at a loss here):

Protocol error in TDS stream

How do I Increase the Timeout Value in SQL?

Network Packet Size: to Fiddle With or Not to Fiddle With

sql
sql-server
ssis
asked on Stack Overflow Sep 3, 2018 by user3115933 • edited Sep 3, 2018 by user3115933

1 Answer

1

Your network appear to be rather unreliable. Since you are already using SSIS, you can easily cut out the middleman (i.e. linked server) and connect to those remote data sources directly from your packages. There is no benefit of using linked servers in this case.

answered on Stack Overflow Sep 3, 2018 by Roger Wolf

User contributions licensed under CC BY-SA 3.0