Configuring Excel Power Refresh for SQL Agent Jobs
Excel Power Refresh within a SQL Agent Job must be done by using a proxy account. Please use the following steps prior to configuring your SQL Agent Job:
1. Create a credential based on your login (found within the Security folder in SSMS):
2. Create a proxy account based on the previously created credential. Select the SSIS Package Integration Services Package subsystem:
3. Set the job step to run as the proxy account:
4. Start the job.
5. If you receive the following error: "Code: 0x00000000 Source: TF Excel Power Refresh Task ExecuteTask Failed: Description: Microsoft Excel cannot access the file <path>.xlsx'. There are several possible reasons: ? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:28:39 PM Finished: 1:28:44 PM Elapsed: 4.735 seconds. The package execution failed. The step failed."
This issue can be resolved by adding a Desktop folder to the following directories:
6. Restart the job.