SSIS Package not running from server

Question

I have an SSIS package that does the following;

1. Detects if a file (excel exists)
2. Deletes file
3. Copies file from a directory into the required one

This package when run locally to test functions correctly there are no issues.

When running on server or as a job I get the error of

Exception has been thrown by the target of an invocation.

I believe the error to be based around the copy code line, as commenting this out still allows the package to run.

My version of Visual Studio is 2013 (unable to upgrade) and the SQL-Server machine is running the latest version of 2016.

The account used to run the job is considered a network administrator - the error persists.

Imports System.IO (etc)

Public Sub Main()
        Dim sourcePath As String = "\\server\File\Template.xlsx"
        Dim destPath As String = "\\server\File\NewFile.xlsx"


        If File.Exists(destPath) = True Then
            File.Delete(destPath)       'deletes current file
        End If
        File.Copy(sourcePath, destPath)
        Dts.TaskResult = ScriptResults.Success
End Sub

I wouldn't expect any issues doing this, as other packages (different functions etc.) work as they should.

Answer 1

Exception has been thrown by the target of an invocation.

This is a generic Script task message. Pretty standard suggestion: consider capturing a real exception text for a better issue analysis via FireError:

Imports System.IO (etc)

Public Sub Main()
Try

    Dim sourcePath As String = "\\server\File\Template.xlsx"
    Dim destPath As String = "\\server\File\NewFile.xlsx"

    If File.Exists(destPath) = True Then
    File.Delete(destPath)       'deletes current file
    End If
    File.Copy(sourcePath, destPath)


Catch ex As Exception
   Dts.Events.FireError(0, "Script Task Example", ex.Message, String.Empty, 0);  
End Try

    Dts.TaskResult = ScriptResults.Success

End Sub

There are plenty of reasons for such code to fail, one of them is related to an in-place package upgrade which didn't upgrade a script task to VSTA 2015 correctly.