2 Hours for an SSIS Job With a Linked Server

1

I have an SSIS job which takes on average 2 hours to complete (I did not write it and have never written an SSIS package). It will occasionally fail with the error:

Executed as user: ICAT\SQL_AgentSvc. Microsoft [...] Started: 12:15:00 AM Error: 2018-01-24 02:00:47.97 Code: 0xC0202009 Source: Data Flow Task 5 Source 21 - sSlip [260] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.". End Error Error: 2018-01-24 02:00:47.98 Code: 0xC0047038 Source: Data Flow Task 5 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source 21 - sSlip returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:15:00 AM Finished: 2:00:48 AM Elapsed: 6334.94 seconds. The package execution failed. The step failed.

Does this have anything to do with the time the job takes? I ask that because my usual resolution is to run the job manually in order to get the data the client is missing the day after it fails, but yesterday that failed and presented the error:

Login timeout expired

The job has several preparation steps and data flow steps. Each preparation step truncates tables in the destination database and then each data flow step grabs all data from identical tables in the linked source and moves the data to the destination. There are 23 tables it operates on. A couple of the tables have over one million records, one has 800k+ and several more have numbers in the tens of thousands.

Control Flow 1

Data Flow Task 1

Destination Editor

sql-server
ssis
asked on Stack Overflow Feb 14, 2018 by CodenameCain

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0