sql server job调用ssis包 “调用的目标发生了异常”

Question

系统：win server 2016

数据库：sql server 2012

office版本：2016

安装了AccessDatabaseEngine

问题：在sql server data tools下调试包是正常进行的，在sql server的job中调用包报错，发布到ssis目录也是一样的错误，“调用的目标发生了异常”

已经尝试过的方案：已经使用了凭证+代理，给的是系统admin权限；尝试简化ssis包里的内容，报错的地方是脚本组件操作excel的语句；

Answer 1

Hi chauncy_zhao,

您是在本机的SQL Server部署的吗？可不可以将您的脚本分享一下用以分析。

该错误信息“调用的目标发生了异常”一般是C#脚本给出的，您可以尝试使用try cache获取详细的异常信息。

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Teige

我是本地部署的 

确实是调用脚本的时候报的错，用SSDT运行是没问题的，job调用的时候报错。脚本用到了Microsoft.Office.Interop.Excel

报错是在这一句

 workbook = excelApp.Workbooks.Open(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

Answer 3

  
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = false;

            string filePath = @"D:/share/AP DCG SC Order Load Report.xlsx";

            Excel.Workbook workbook = null;
            Excel.Worksheet sheet = null;
            Excel.Range range = null;

            object oMissiong = System.Reflection.Missing.Value;

            workbook = excelApp.Workbooks.Open(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
            //int wsCount = workbook.Worksheets.Count;
            System.Threading.Thread.Sleep(5000);
            //sheet = (Excel.Worksheet)workbook.Worksheets[7];
            sheet = (Excel.Worksheet)workbook.Worksheets["AP Detail Data"];
            sheet.Activate();
            //int rc = sheet.Rows.Count;
            int rc = sheet.Application.get_Range("a65536", oMissiong).get_End(Excel.XlDirection.xlUp).Row;

Answer 4

 的身份执行。 Microsoft (R) SQL Server 执行包实用工具  版本 11.0.2100.60 (32 位)  版权所有(C) Microsoft Corporation。保留所有权利。    开始时间:  20:55:08  错误: 2019-03-05 20:55:56.94     代码: 0x00000001     源: Script Task delete AP Detail Data 5 Microsoft.Office.Interop.Excel.Workbook Open(System.String, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object, System.Object)     说明: 异常来自 HRESULT:0x800A03EC  错误结束  错误: 2019-03-05 20:55:56.95     代码: 0x00000006     源: Script Task delete AP Detail Data 5      说明: 脚本返回了失败结果。  错误结束  DTExec: 已返回包执行 DTSER_FAILURE (1)。  开始时间:  20:55:08  完成时间: 20:55:56  占用时间:  48 秒.  包执行失败。.  该步骤失败。