0

I'm new in the area and I need to run a script for SSIS project.

Here's the script:

public void Main()
        {
            var server = Dts.Variables["ExcelFileName"].Value as string;

            var xlApp = new Microsoft.Office.Interop.Excel.Application();

            xlApp.Workbooks.Open(server);
            xlApp.Run("ResetReport");
            xlApp.ActiveWorkbook.Close(true);
            xlApp.Quit();

            Dts.TaskResult = (int)ScriptResults.Success;
        }

However when I tried to run SSIS package I'm getting Run time error. I was looking for an answer but could not find the one that would help me.

I have MS Visual Studio 2012, Net Framework 4.8.

Can somebody assist me with fixing this error?

Vladislav Mikhailov
  • 33
  • 1
  • 3
  • 10
  • It looks approximately correct - has it ever worked? What is a value for `ExcelFileName`? Do you have Office (Excel) installed on the machine this code runs on and does the bit version align with SSIS execution bit version (32 vs 64)? – billinkc Feb 02 '21 at 20:45
  • @billinkc The value of 'ExcelFileName' is "w:\Work\...\FHIA AIG Monthly Report.xls" I have MS bit version 64 and SSIS as well 64. When I ran DTS package it works fine, however while trying to convert (manually) to SSIS it doesn't work and raise an error. – Vladislav Mikhailov Feb 03 '21 at 08:24
  • Add a try/catch block and messagebox to output the error message so you can see what the actual error is. Or what I do for more advanced script tasks (or ones I have issues with) is build it as a console app, and you can debug and step through the code much easier. Some changes would need to be made like the variable value cant come from dts.variables, you would just hard code it for the testing. – Brad Feb 03 '21 at 12:31
  • Is the W: a physically attached drive or a mount (likely the latter)? If so, the account running the SSIS Package may not have access to the drive (permissions) or it may not have that drive mapped (use a UNC path aka \\server\share\Work\...\Report.xls) – billinkc Feb 03 '21 at 15:07
  • @billinkc the W: is physically attached drive. Should I grant some additional access to the Dtsx package? I'm not really know how to code on C# I'm just learning and this code I took from another similar package. I've read somewhere there should be updated Analysis Service up to 12.0. I tried to do this but could not as I have MS 2012 and Analysis Service 12.0 is for MS 2016. Should I try to install MS 2016? – Vladislav Mikhailov Feb 03 '21 at 20:47

1 Answers1

0

I figured it out by making following things:

  1. Go to Parameters
  2. Apps
  3. Apps and features
  4. Find your Microsoft office, click on it and choose "Change"
  5. Click "Quick repair".
  6. Run again your package.

This helped me and hope this will help someone else.

Vladislav Mikhailov
  • 33
  • 1
  • 3
  • 10