Getting Runtime Errors When Trying to Refresh Excel in SSIS Script Task

Question

I am trying to refresh queries in an Excel via using SSIS. I created a script task as follows:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Windows.Forms;
using System.IO;

public void Main()
		{
            Microsoft.Office.Interop.Excel.Workbook wb = new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            string fileName = (string) Dts.Variables["$package::ExcelFileName"].Value;

            Dts.TaskResult = (int)ScriptResults.Success;

            try
            {

                wb = excel.Workbooks.Open(fileName);
                Thread.Sleep(3000);

                wb.RefreshAll();
                Thread.Sleep(10000);

                wb.Save();
                wb.Close();
                excel.Quit();

            }
            catch(Exception exception)
            {
                MessageBox.Show("I am catching the error");

                using (StreamWriter sw = File.CreateText("C:\\log\\" + "ErrorLog_" + DateTime.Now.ToString("yyyMMddHHmmss") + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
		}

In the task I have ReadOnlyVariables as: $Package::ExcelFileName

I have that variable set in my Parameters and it contains the path and filename of the Excel file.

When I run the script I get the following error:

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()  

I also have this error in the output:

Error: 0x1 at Refresh Consolidated File: Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

The error makes zero sense and I don't even know where to begin to figure out what's going on.

HELP!

Answer 1

I had multiple things going on....

First was how I was accessing the file. I changed to use:

m_objExcel = new Excel.Application();
                m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
                m_objBook = (Excel.Workbook)m_objBooks.Open(fileName,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing);
                Thread.Sleep(3000);

                m_objBook.RefreshAll();
                Thread.Sleep(10000);

                m_objBook.Save();
                m_objBook.Close();
                m_objExcel.Quit();

Secondly is that the fileName variable is not pulling in the string correctly. When I hardcoded the path/filename it works.