SSIS intermittently failing on dataflow task, post file system task (External table is not in the expected format)


I have a few SSIS packages that use this solution and are deployed and executed on SQL 2016 server via SQL agent, they mostly run fine but on the odd occasion one will fail.

The failure always occurs on the dataflow task after a copy file - file system task

File system task

The copy file task is essentially moving a excel template from one nas file share location to another this file will be used as the destination in the following dataflow task.

enter image description here

enter image description here

Error Details

SSIS Error

An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".

My thinking is that the destination file has not been fully copied down before the dataflow task is executing. Has anyone else experienced anything similar of have any guidance?

I have now added a sql task with a delay timer for 3 seconds just to pause before the next step to see if this help, bit of a hack but i would like to understand what is causing the errors.

Thanks In advance

asked on Stack Overflow Oct 19, 2020 by Mike Harris

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0