C# script cannot be executed in my job when deployed from ssis to Sql server

0

I have a C# script task in my SSIS package that I can run on my computer without any problem. But when I deploy it on my server and execute the package from the sql job I get an error. With the try/catch clause I get this in a txt file:

Message :Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).<br/>
StackTrace :   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.Activator.CreateInstance(Type type)
   at ST_86c290165eda4c9a962b68fdf181801f.ScriptMain.Main()

Can somebody explain me what the problem is please?

The drivers ACE.OLEDB 12.0 and 16.0 are installed on the server (SQL server 2014 SP2 CU11)

How can I change my script to change font without using interop ?

Here is my C# script

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

namespace ST_86c290165eda4c9a962b68fdf181801f
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {   
        public void Main()
        {
                String FilePath = Dts.Variables["User::excelPath"].Value.ToString();
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(FilePath);
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;

                Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);
                Excel.Range excelCell = (Excel.Range)excelWorksheet.Cells;
                excelCell.Font.Name = "Arial";
                excelWorkbook.Save();
                excelWorkbook.Close(true);
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}
c#
sql-server
ssis
asked on Stack Overflow May 22, 2018 by C. Men • edited May 22, 2018 by C. Men

1 Answer

0

The CLSID {00024500-0000-0000-C000-000000000046} refers to Microsoft.Office.Interop.Excel.

I'd suggest you need to install Excel on your server. I don't know if there is a specific 'Interop' installation but it may be available in Excel's custom setup.

answered on Stack Overflow May 22, 2018 by spodger

User contributions licensed under CC BY-SA 3.0