SSIS Package not running from server

user_2261755 0
Viewed 1141

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.

script-task etl ssis vb.net sql-server
2 Answers

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.

user_50216870

The error exists due to versioning differences between the SSIS Package writtne in Visual Studio 2013 and SQL Server 2016.

Upgrade errros can be found within the message portion of the error report.

user_22617550
Related