SSIS Package fails when called from task scheduler via a batch file because of a script task

Question

I have an SSIS package being called from a batch file and I am trying to schedule it via the task scheduler. The package works fine in Visual Studio, and it works when I execute the batch file, but it fails when I run the package through the scheduler. I've read all other post on this topic and I don't see anything relevant to mine, the problem is not configuration of the task scheduler properties (i.e the account it's using, run at highest privilege, start in directory, etc..).

I run multiple packages successfully through the task scheduler with no issues, this one just happens to use a c# script task that I had to add an assembly reference to and I think that's what is causing the problems when the package runs via the scheduler as the other packages use c# script task without issue but I did not add any assemblies.

This is the C# script which is used to format an excel spreadsheet after it's populated with data.

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

#endregion

namespace ST_2bdf93d5542441248076f053703d32c9
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            int lastUsedColumn = 0;

            string inputFile = (string)Dts.Variables["RecommendationFileName"].Value;
            string RecommendationName = (string)Dts.Variables["RecommendationName"].Value;

            Excel.Application ExcelApp = new Excel.Application();
            Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);

            //ExcelApp.Visible = true;  //Use this to show the excel application/spreadsheet while the package is running.  Not good for prod, just testing.
            ExcelApp.Visible = false;

            Excel.Worksheet xlWorkSheetFocus = (Excel.Worksheet)ExcelWorkbook.Worksheets.get_Item(3);
            xlWorkSheetFocus.Activate();
            xlWorkSheetFocus.Select(Type.Missing);
            Excel.Range usedRange = xlWorkSheetFocus.UsedRange;

            foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
            {
 
                ExcelWorksheet.Columns.AutoFit(); //Autofit the column to width for each worksheet, we adjust some column widths manually later.

                if (ExcelWorksheet.Name == "Recommendations")
                {
                    ExcelWorksheet.Cells[1, 4].EntireColumn.ColumnWidth = 125;
                    ExcelWorksheet.Cells[1, 4].EntireColumn.WrapText = true;
                }

                if (ExcelWorksheet.Name == "Passed")
                {
                    ExcelWorksheet.Cells[1, 4].EntireColumn.ColumnWidth = 125;
                    ExcelWorksheet.Cells[1, 4].EntireColumn.WrapText = true;
                }

                if ((ExcelWorksheet.Name != "Recommendations") & (ExcelWorksheet.Name != "Passed"))
                {

                    // Find the last real column in each worksheet 
                    lastUsedColumn = ExcelWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                    Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious,
                    false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;

                    ExcelWorksheet.Rows["1"].Insert(); //insert empty top row
                    ExcelWorksheet.Rows["2"].Insert(); //insert empty second row
                    ExcelWorksheet.Rows["3"].Insert(); //insert empty second row

                    ExcelWorksheet.Cells[1, 1].Interior.Color = 0x565656; //Row 1 = Dark Gray
                    ExcelWorksheet.Cells[2, 1].Interior.Color = 0x565656; //Row 2 = Dark Gray
                    ExcelWorksheet.Cells[3, 1].Interior.Color = 0x3ad7bd; //Row 3 = Green
                    ExcelWorksheet.Range[ExcelWorksheet.Cells[4, 1], ExcelWorksheet.Cells[4, lastUsedColumn]].Interior.Color = 0xCECECE; //Row 4 = Light Gray

                    //Bold the Fourth row of each spreadsheet (column headers are here)
                    ExcelWorksheet.Range["A4"].EntireRow.Font.Bold = true;

                    //Add a link back to the Recommendations page in row 2
                    ExcelWorksheet.Hyperlinks.Add(ExcelWorksheet.Cells[2, 1], "#Recommendations!A2", Type.Missing, "Return to Recommendations", "Return to Recommendations");

                    //Change row 1 to White, Bold, and 12pt font Arial, this is the report Title
                    ExcelWorksheet.Cells[1, 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                    ExcelWorksheet.Cells[1, 1].Font.Bold = true;
                    ExcelWorksheet.Cells[1, 1].Font.size = 12;
                    ExcelWorksheet.Cells[1, 1].Font.Name = "Arial";

                    Excel.Range formatRange;
                    formatRange = ExcelWorksheet.get_Range("c1", "c1");
                }

            }
            ExcelWorkbook.Save();

            GC.Collect();
            GC.WaitForPendingFinalizers();

            ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
            Marshal.FinalReleaseComObject(ExcelWorkbook);

            ExcelApp.Quit();
            Marshal.FinalReleaseComObject(ExcelApp);
        }
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

And here are the references I added to this script task:

My question is, knowing that it has something to do with these references, does anyone understand why this happens? I am running the task with a local admin account and the batch file is on the local filesystem, everything else works in the package until this script task when using the task scheduler. I tries to copy the Excel Interop DLL file to the same folder as the batch file and re-added the reference to see if maybe that was the issue to no avail. My other script task which I did not have to add any assembly references to work just fine this way.

Answer 1

For me, it worked when i gave the fully qualified path to dtexec in the batch file whereas before i had just dtexec /file, now i have "c:\program files\...\dtexec.exe" /File "c:\..." and it worked as i was using 32 bit exe or make sure your PATH variable has 32 bit dtexec path first

Answer 2

I came to realize that Interop was not going to work headless, either through the agent or task scheduler, so I switched to ClosedXML, built a console app, and execute it that way and it works.

Answer 3

ding ding ding

I had to add an assembly reference to and I think that's what is causing the problems

Correct. You are using the Excel object model, via Microsoft.Office.Interop.Excel, to build/modify an Excel Workbook. The scheduler server does not have Office installed so the package fails as it can't find the required libraries. The correct resolution is to install Office on the server.

I tries (sic) to copy the Excel Interop DLL file to the same folder as the batch file

You do not want to "solve" the problem by copying the required assemblies to the scheduler. Even if you get all the required files installed, you've now opened your company up to failing an audit.

Office isn't free, the fine folks in Redmond built it, your organization will want to pay for it because paying upfront is so much cheaper than an audit finding a willful violation. Compare and contrast these conversations

"Oh yeah, we installed XYZ on this box an forgot about it" Auditors: ok, fine, true up your licensing and pay for what you're using. $

"Oh yeah, we mirrored on the libraries over there, installed them to the GAC, etc" Auditors: So it wasn't just an accident, that was deliberate and ignorance is not a defense. You owe us licensing fees and the following penalties. $$$