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):
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.
User contributions licensed under CC BY-SA 3.0