Running Excel Macro Code by SSIS package configured with SQL Server Job agent

Question

I am trying to execute an Excel macro. It executes successfully on Visual Studio Data Tools 2012. But When I configured this package with SQL Server Job agent and try to execute this, it throws an error.

Executed as user: *****. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:07:32 AM Error: 2016-05-20 01:07:32.99 Code: 0x00000001 Source: Trigger Macro in BAC_HE Excel Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:07:32 AM Finished: 1:07:32 AM Elapsed: 0.297 seconds. The package execution failed. The step failed.

And Error logged in log file is

OnError,CEAGPDS0102,*****,Trigger Macro in BAC_HE Excel,{1aed82a9-3da5-4044-b1f1-573385d7c8b7},{8AAF675E-DA4F-454D-8CF7-61E6B511C0AA},5/20/2016 6:57:18 AM,5/20/2016 6:57:18 AM,1,0x,Exception has been thrown by the target of an invocation.

Answer 1

Does the service account for the SQL Server Agent have access to the directories for the objects being modified by the Excel macro? My experience is when code works in your development tools but fails when running through the SQL Server Agent, it is because the service account for the SQL Server Agent is missing permissions that your user account has when running interactively in the development tools.