调用的目标已引发异常-SSIS脚本任务错误 [英] Exception has been thrown by the target of an invocation - SSIS Script Task Error

问题描述

我有以下脚本任务，使用C＃刷新SSIS包中Excel中的数据透视表。该程序包在Visual Studio和程序包实用程序中运行良好。但是，当我将此程序包部署到SSIS目录并安排SQL Server Agent作业时，它在脚本任务部分失败，并显示非常普通的错误消息

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.

我的脚本任务代码

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

请帮助。

推荐答案

选项A-服务器上尚未安装Microsoft Office。技术上的答案是安装并许可Office，但这通常不是一件好事，正如我在这里提到的 https://stackoverflow.com/ a / 37870251/181965

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

选项B-已安装Office，但是您正在以与已安装版本不同的位方式执行SSIS软件包。如果安装了32位版本的Office（默认设置），则需要在类似的域中运行SSIS程序包。 SQL Server盒上SSIS的默认执行版本为64位，因此可以使用32运行时或重做Office安装以匹配默认调用。在上述答案中也得到了解决

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

选项C-权限。运行SSIS软件包的帐户是否可以访问位于C：\SSIS\的文件系统？该帐户是否对该位置具有写权限？

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?

选项D -位置和文件是否存在？

Option D - Does the location and the file exist?

选项E-执行软件包的帐户是否在活动目录中检查了InteractWithDesktop位-假设它是服务帐户

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

选项F-您在服务器上运行与针对它开发的SSIS版本相同吗？

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

选项G -一切实际上都运行良好，但是由于您已注释掉Main中的返回码，因此任务失败。

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.

这篇关于调用的目标已引发异常-SSIS脚本任务错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！