Exception has been thrown by the target of an invocation - SSIS Script Task Error

Question

I have the following script task using C# to refresh pivot table in Excel in a SSIS package. The package runs well in the Visual Studio as well as the package utility. But when I deployed this package to SSIS Catalog and schedule a SQL Server Agent job, it failed on the script task part with a very generic error message

Exception has been thrown by the target of an invocation

I have researched a couple of other Q&As but our situations differ a lot.

My scrip task code

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_0126969a11e546b3bbde047669039ab5
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            //Dts.TaskResult = (int)ScriptResults.Success;

            ExcelRefresh(@"C:\\SSIS\\MTD.xlsx");
        }

        private void ExcelRefresh(string Filename)
        {
            object NullValue = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.DisplayAlerts = false;
            Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
               Filename, NullValue, NullValue, NullValue, NullValue,
               NullValue, NullValue, NullValue, NullValue, NullValue,
               NullValue, NullValue, NullValue, NullValue, NullValue);
            Workbook.RefreshAll();

            Workbook.Save();
            //Workbook.SaveAs(DestinationPath, NullValue, NullValue, NullValue, NullValue, 
            // NullValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
            //NullValue, NullValue, NullValue, NullValue, NullValue);
            Workbook.Close(false, Filename, null);
            excelApp.Quit();
            Workbook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        }
    }
}

Please help.

billinkc billinkc · Accepted Answer · 2018-04-11T01:24:47

Option A - Microsoft Office has not been installed on the server. The technical answer being "install and license Office" but that's generally not a good thing as I mention over here https://stackoverflow.com/a/37870251/181965

Option B - Office is installed but you are executing the SSIS package in a different bit-edness than the installed version. If you installed the 32 bit version of Office, which is the default, then you need to run the SSIS package in a similar domain. The default execution version of SSIS on a SQL Server box will be the 64 bit so either use the 32 runtime or redo the Office installation to match the default invocation. Also addressed in the aforementioned answer

Option C - Permissions. Does the account that is running the SSIS package have access to the file system at C:\SSIS\ Does the account have write permission for that location?

Option D - Does the location and the file exist?

Option E - Does the account that is executing the package have the InteractWithDesktop bit checked within active directory - assuming it's a service account

Option F - Are you running the same version of SSIS on the Server as you developed against?

Option G - Everything is actually running fine but since you've commented out the return code in Main, the task is failing.

Those are my top thoughts of where this has gone haywire.

Exception has been thrown by the target of an invocation - SSIS Script Task Error

1 Answers