0

I have a script task (excerpt code below) which is supposed to open an Excel Workbook:

        If CountOfT > 0 Or RowsUnallocated > 0 Then
        Workbook = Workbooks.Open(BrkStTmpFolder + "NewTemplate.xlsx")
        'MsgBox("Break Point")
        'Workbook.Save()
    Else Exit Sub
    End If

    ExcelApp.visible = False
    ExcelApp.DisplayAlerts = False
    Workbook.DoNotPromptForConvert = True
    'MsgBox("Break Point")

The code basically is supposed to open the Excel Template which then goes on to be populated by other Script Tasks whilst its open from some data in SQL Server.

When I run it in Visual Studio it runs perfectly but when I deploy to SQL Server and try to execute it there it fails at this particular Script Task with some like "Exception has been thrown by the Target of an Invocation"

Insight and help appreciated

Thanks

PS

I also have this code to kill all running Excel processes before opening the Excel Template

        Dim pName As String = "EXCEL"
    Dim psList() As Process
    Try
        psList = Process.GetProcesses()

        For Each p As Process In psList
            If (pName = p.ProcessName) Then
                'MsgBox(p.ProcessName) 'TEST LINE
                p.Kill()
                'MsgBox("Process Found")
            End If
        Next p

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
Shaye
  • 179
  • 13
  • 2
    Does the target server have a licensed installation of Microsoft Office (as you're using the office automation in your scripting, those DLLs need to be on the box and to get them on the box, you're looking at licensing it) – billinkc May 19 '20 at 16:55
  • Also, how do you execute the package in SQL Server, from a scheduled job? – Gigga May 19 '20 at 17:22
  • @billinkc. I just found out some issues which could be causing the error. We have a strange configuration where the SQL Server is hosted on AWS. When I'm developing I'm not logged onto the server directly but via a kind of virtual machine which explains why I'm able to run the package on VS because I have Office Installed but on the actual AWS server Office is not installed. Thanks – Shaye Jun 03 '20 at 10:19
  • @Gigga. I initially tried to execute it from the server by right clicking. But the issue is as billinkc pointed out – Shaye Jun 03 '20 at 10:20

0 Answers0