Southern_Gentleman
asked on
Exception thrown when I Execute excel macro in SSIS
I am receiving error: exception has been thrown by the target of an invocation. I'm trying to execute a macro in an Excel workbook through an SSIS package. It runs fine when i run it in Visual Studios but when i schedule it in SQL Agent this error comes up. I made sure that my Excel is running 64 BIT.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
public void Main()
{
string filename = Dts.Variables["EmailAttachment"].Value.ToString();;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filename);
xlApp.Visible = false;
xlApp.Run("MyMacroName");
xlWorkBook.Save();
xlWorkBook.Close(true);
xlApp.Quit();
System.Threading.Thread.Sleep(15000);
Dts.TaskResult = (int)ScriptResults.Success;
}
ASKER
that was a copy and paste error. It works fine in visual studio but not in SQL Agent. I get the exception error. I also changed the variable to ["User::EmailAttachment"], same error. My location of the excel attachment is accessible for read/write. I'm running all this on Windows Server 2012 so i'm not sure if that has anything to do with it and if I have to do some other configurations.
are you able to do a try. .. catch in your codes and write the exception details into a log file, so you can have more details what this error is all about?
ASKER
no i haven't. I'm having trouble trying to put error in a log file. Is there an example pertaining to this?
ASKER
It's baffling, i can execute it in SSMS just fine in the SSISDB, i can execute it in Visual Studio, but in SQL Agent is where the exception error occurs. Is there issues that have come up when i schedule a package?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dts.Variables["EmailAttach