Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS

When executing the Open function. I received the following exception:

Microsoft Office Excel cannot access the file ‘e:\report\report.xls’.

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.

SOLUTION:

For Windows Server x64: Create the following directory:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows Server x86: Create the following directory:

C:\Windows\System32\config\systemprofile\Desktop

Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead

.NET 4.0 allows primary interop assemblies (or rather, the bits of it that you need) to be embedded into your assembly so that you don't need to deploy them alongside your application.

Reason:
In most cases, this error is the result of code which tries to instantiate a COM object.

For example
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();

Solution #1
Typically, in .NET 4 you just need to remove the 'Class' suffix and compile the code:
Excel.Application xlapp = new Excel.Application();

Solution #2
For whatever reason, this assembly can't be embedded. Just open the Properties tab for the assembly in Visual Studio 2010 and set "Embed Interop Types" to "False".

SQL Agent + Excel issue

We have a SSIS package which is suppose to execute macro. This SSIS package works fine when it is executed from the development environment but fails with following message when package is scheduled as job.


System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'XYZ.xls'. 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.

Solution/Workaround:
it is a workaround for a bug in Windows:

1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
2. Set Full control permissions for directory Desktop (for user "SYSTEM")

Please do this for both 32 and 64 bit folder.

Hope it helps