I develop a package in SSIS, I need to use format excel sheets and I am sure many of you will using it because of business people needing data in pretty form. Any ways, till this part when I run from SSIS it works like a champ. But as soon as I scheduled in SQL agent on server it started to fail with below error.
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. 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. at Microsoft.Office.Interop.Excel.Workbooks
After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.
Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. 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. at Microsoft.Office.Interop.Excel.Workbooks
After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.
Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.
Solution(1)
Create folder called "Desktop" really dont know why but it works.
Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop” (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop” (for 32 bit Windows)
In my case I had created folder on both locations and give read write permissions on it
P.S
You will still see this error even if you have created the folder but the permissions are not correct.
