This issue has been giving me a bit of grief lately – I’ve been doing some funky things with Excel and SQL Server Integration services to extract data from the web, do some pre-processing, then import it into a database and writing a report (in SSRS).
Apparently Excel is set up to require that automation tasks are not scheduled – The app throws an error onto the instance running in the scheduled task, but as you cannot see it you cannot troubleshoot it
I was looking around the interweb for a solution and came across this thread (Run Powershell script as Scheduled task, that uses Excel COM object) in TechNet which gave me the answer that worked:
You have to create a folder (or two on a 64bit-windows):
(32Bit, always required)
My real gripe here is that it would be so much easier if (when you are logged into the server using the account the task runs under) it actually showed you the dialog (you know, like a “troubleshooting view” which exposes everything directly to the interface but one you could switch on and off as part of the task’s settings). Interactive setting (in the task) does not work in this case, and it’s really frustrating when no errors appear in the event log or on the screen.