Run Excel Macro using SSIS throught SQL Jobs failed

Question

I have a task to create Excel VBA and run that macro on SSIS. I can successfully run the Excel Macro from the SSIS Package, but I have a problem when I run that SSIS on SQL Jobs. I have created Credential, Proxy SSIS, and set SSIS unprotected, but it always fails.

Error Message

Executed as user: HARNANDA7-PC\HARNANDA7.

Microsoft (R) SQL Server Execute Package Utility

Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.

Started: 11:56:14

Error: 2014-05-28 11:56:17.61 Code: 0x00000001

Source: Script Task

Description: Exception has been thrown by the target of an invocation. End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 11:56:14 Finished: 11:56:17 Elapsed: 2.652 seconds.

The package execution failed. The step failed.

but if i create Folder named "Desktop" on C:/Windows/System32/config/systemprofile/ or C:/Windows/SysWOW64/config/systemprofile/ then the SSIS package runs successfully through SQL Jobs.

I'm confused as to what the problem is here. Can anyone help?

Big thanks

Answer 1

For whatever reason (no one seems to know from what I can gather), Excel requires that the desktop folder be present on your machine. It must use it as a global setting or possibly a default setting for creating files or temporary files. If that folder doesn't exists then (and this is just a guess) excel either has issues creating these temporary files or requires that the Desktop directory exists and, in both cases, throws an error if the Desktop directory does not exist.

I doubt that they would want to make something like this an explicit feature, so it is most likely a bug with Excel.