Link to home
Start Free TrialLog in
Avatar of Southern_Gentleman
Southern_GentlemanFlag for United States of America

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;
        }

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

duplicate semi-colon there?

Dts.Variables["EmailAttachment"].Value.ToString();;
Avatar of Southern_Gentleman

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?
no i haven't. I'm having trouble trying to put error in a log file. Is there an example pertaining to this?
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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial