If you have an SSIS package which fails when run on a schedule but succeeds when executed interactively in Visual Studio/BIDS/SSDT, it may produce an error like this:
Executed as user: DOMAIN\user. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:52:25 Error: 2017-02-06 10:52:26.26 Code: 0x00000001 Source: Open Spreadsheet and Run Macro Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:52:25 Finished: 10:52:26 Elapsed: 0.485 seconds. The package execution failed. The step failed.
The issue is a type of permissions error. Excel needs to have its permissions changed via the DCOM applet in Control Panel. By default it is on “The launching user.” This needs to be changed to a user with more permissions, in this case we have used the service account used by SQL Agent.