I am seeing the below error coming from SSIS package at random times. There are around 15 data flow tasks within one package which run parallel. The task is to copy data from one SQL server and insert it in another SQL server. The package runs perfectly through SQL job but I get the below error at random times. I did a ping test and confirmed that there is no packet loss when this error occurs. The package error occurs at random task within those 15 and is different each time.
These 3 errors are shown in execution log every time the package fails:
|Error: <Sproc> failed validation and returned error code 0xC020801C.|
|Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB_Name" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. |
|_Master: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 Server Native Client 11.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
I searched online and there no issues here with 32-bit vs 64-bit since there is no excel or other databases involved other than SQL server as source and destination. No packet loss using ping test during the error. The job runs successfully 95% of the day but fails at random times.