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.
Best Answer
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.