SSIS Run Excel Macro Error

Question

I have an SSIS package that populates an Excel file. The package is run from a SQL Server Agent job.

It all runs fine, but now I have added a Macro to the Excel file. This macro causes the Package to fail when it is run. Are there any permissions I need to add to allow the SQL Agent account to run the macro?

This is the error:

Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

After installing Excel, the error is as follows:

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.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)

Answer 1

Solution:

https://stackoverflow.com/questions/881132/retrieving-the-com-class-factory-for-component-failed

1. In DCOMCNFG, right click on the My Computer and select properties.
2. Choose the COM Securities tab
3. In Access Permissions, click "Edit Defaults" and add Network Service to it and give it "Allow local access" permission. Do the same for \Users.
4. In launch and Activation Permissions, click "Edit Defaults" and add Network Service to it and give it "Local launch" and "Local Activation" permission. Do the same for \Users