SSIS C# script task to open Excel causes invocation error

1
votes

I have an SSIS package that has a script task, that reads from an Excel. I am getting the below errors and can't seem to find the issue.

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[]
arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

I have narrowed the issue down to these 2 lines, where I am setting the Excel application.

Excel.Application xlApp;
xlApp = new Excel.ApplicationClass();

It's the setting of the value of xlApp that seems to be causing the error.

I am using Visual Studio 2017.

Can anyone help me out on this?

1
c#excelssisscript-taskssis-2017
Why are you using SQL Server (Microsoft.SqlServer) with Excel? – jdweng
I am reading in an excel from a folder, but the they have the tendency to rename the sheet. So the Script task is to read the sheet name so it can properly read from the file. – Caveman42
So you have to open each excel file ".xls" and then get enumerate through the sheet names. – jdweng
I figured it out after. The package was being run by a user through a job on SSMS. I had to create the user that was running the package in the job on the server it was running on. That seemed to give it access to the Excel functions on the server. – Caveman42

1 Answers

1
votes

Exception has been thrown by the target of an invocation.

Is an general exception that is thrown by Script Task when an error occurred

Reading the real error message

To read the main error message you can add a try catch clause into your code and use Dts.FireError() method to throw the real exception.

public void Main()
{
    try{

        Excel.Application xlApp;
        xlApp = new Excel.ApplicationClass();

        //...rest of code here

        Dts.TaskResult = (int)ScriptResult.Success;

    }catch(Exception ex){

        Dts.FireError(0,"An error occured", ex.Message,String.Empty, 0);
        Dts.TaskResult = (int)ScriptResult.Failure;

    }


}

Trying to figure out the issue

  1. Try using Excel.Application instead of ApplicationClass

    Excel.Application xlApp = new Excel.Application();

  2. Make sure that the Microsoft Excel is installed on the machine and the Interop assemblies are registered in GAC