0

I have an SQL Server 2012 SSIS package with a script task to refresh an excel file. When I run manually in Visual Studio it executes fine, no problems.

When I execute the package through an SQL Agent job, it fails with the following message:

Package execution on IS Server failed. Execution ID: 357243, Execution Status:4.

The message in the Integration Services Catalogs:

Script Task Error: Exception has been thrown by the target of an invocation.

All other packages that don't have a script task can be successfully executed via SQL Agent job.

I've tried the solution on link:
https://social.msdn.microsoft.com/Forums/en-US/b81a3c4e-62db-488b-af06-44421818ef91/excel-2007-automation-on-top-of-a-windows-server-2008-x64?forum=innovateonoffice

by setting up folders: 

C:\Windows\SysWOW64\config\systemprofile\Desktop  
C:\Windows\System32\config\systemprofile\Desktop

I've also followed the suggestions on
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a20219e-4a90-41be-acfe-b8846dc2c38a/error-while-executing-a-ssis-package-which-contains-a-script-task-through-sql-server-agent-job?forum=sqlintegrationservices with no luck.

I'm hoping someone will have another suggestion.

My script task is:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

    /// </summary>
    public void Main()
    {
        string TemplatePath = Dts.Variables[@"User::TemplatePath"].Value.ToString();

        Excel.Application MyApp = new Excel.Application();
        Excel._Workbook Template = null;

        MyApp.Visible = false;
        Template = MyApp.Workbooks.Open(TemplatePath);
        Template.RefreshAll();
        Template.Close(true);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
T Hess
  • 11
  • 1
  • 5
  • 1
    Do you have `Microsoft.Office.Interop.Excel` installed on the server thatruns the job? – Nick.Mc Mar 30 '17 at 06:34
  • Hi Nick, Yes the Microsoft.Office.Interop.Excel is installed on the server that I'm running the package from – T Hess Mar 30 '17 at 22:33
  • That's the SQL server that _hosts_ the SQL Agent job right? I have to say that any automated process using Excel like that is at high risk of failure. It could be that the `RefreshAll` is failing because the SQL Agent windows account doesn't have access rights (to files or database) to do the refresh step. Are you sure you have a full understanding of how SQL Agent works, and how it interacts with file shares, and how it is actually running on the SQL Server, not the server you call it from etc.? That's usually the number one issue when something runs on VS but not in a job. – Nick.Mc Mar 31 '17 at 01:13
  • For example you should always use UNC's instead of drive letters, and your SQL Agent service account often needs to be changed to a network account, For further troubleshooting I suggest you build a script task without Office (that does basically nothing) and test that in a job. If it works, the issue is related to Excel. If it doesn't, you know the issue is related more broadly to scripts. After that you might want to add logging for each line (this might show how https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task) – Nick.Mc Mar 31 '17 at 01:17
  • Hi Nick, Thanks for your help with this. Yes, the Microsoft.Office.Interop.Excel is on the same server that hosts the SQL Agent job. I've tested a basic Script Task, deployed to SQL Agent Job and it ran successfully. It seems the issue is related to Excel. In SSMS, Under Credentials, I have a Credential name called "SSIS Proxy" that is registered against my user login and is used for all our packages. I definitely have access to the folder and excel file name. – T Hess Mar 31 '17 at 02:38
  • So now you need to find out the line of the error. The brute method way to do this is to simply cut out lines of script, deploy and test. It will be interesting to see of it is when it first tries to use the interop or when it tries to refresh. – Nick.Mc Mar 31 '17 at 03:56
  • Oh, and do you have Excel installed on the server also? (This would actually be out of the ordinary if you did). My understanding is that Interop lets .Net talk to COM. But you still need Excel installed for the Excel COM bit. http://stackoverflow.com/questions/11448197/how-to-use-microsoft-office-interop-excel-on-a-machine-without-installed-ms-offi – Nick.Mc Mar 31 '17 at 03:58
  • Again... whatever you're doing (basically Excel automation on a server) is not recommended. Do you have some kind of Excel linked data system that you don't want to replace with a proper system? You most likely could replace it with more robust tools but I understand if that is not an option. Perhaps you could explain what you are really trying to solve - the big picture. – Nick.Mc Mar 31 '17 at 04:00
  • Thanks Nick, I'll work on breaking down the task line by line. The big picture is, we have an excel report that is distributed. This report is linked to a database and currently we have to either manually run the package, or open the excel file, hit refresh then save. We're trying to automate so we don't need to do these steps. The end user of the report, who we distribute to (via the package), does not have access to do this excel refresh themselves. Open to ideas if there's another solution. – T Hess Mar 31 '17 at 04:46
  • Firstly, depending on your infrastructure team, you might find it difficult to get Excel installed on your SQL Server. If that's not an issue then really you just need to flag that this kind of thing is not a 'enterprise' reporting solution, that it's a 'technical risk', and it should be converted to something more robust like SSRS. But it depends on how complicated the report is, how often it changes, if users need to add custom narration, and how important it is to have robust reporting etc. Another suggestion is that you might get a _lot_ of benefit out of rebuilding it in PowerBI – Nick.Mc Mar 31 '17 at 04:55
  • SSRS is not an option unfortunately as the end users of this particular report do not have access to the location where our IT require us to deploy SSRS reports. Excel is installed on the server, but is there something additional I need to do to have SSMS/SQL Server Agent to recognise it? – T Hess Mar 31 '17 at 05:09
  • Nothing special that I'm aware of but I do suggest that you log on to the SQL Server with your SQL Agent service account (which as I understand is yourself) and open Excel - you might find it's prompting you about licences or something. You should also look in the windows event log and the SQL Server log as Excel or SQL might be reporting something in there. When you work out whether it's the InterOp call or the Refresh call it will also be very enlightening. – Nick.Mc Mar 31 '17 at 05:13
  • Have found the issue via the SQL Server log, thankyou for suggesting as I've never used this part before. Error: "The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {00024500-0000-0000-C000-000000000046} and APPID {00020812-0000-0000-C000-000000000046} to the user ACCOUNT-01\c887954 SID (S-1-5-21-2057967664-1157253723-1077494903-148933) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool." – T Hess Apr 03 '17 at 05:36
  • Good work finding that entry.Just a word of advice, when you're stuck there is almost always more info in a log _somewhere_. It's shame the SSIS error message is so useless. Here's a link you might find useful: https://social.technet.microsoft.com/Forums/systemcenter/en-US/dfc465bc-7bbd-483e-b98b-2ba56fa98313/the-applicationspecific-permission-settings-do-not-grant-local-launch-permission-for-the-com-server?forum=configmgrgeneral – Nick.Mc Apr 03 '17 at 05:58

0 Answers0