Error in a SQL Job, Exception has been thrown by the target of an invocation

0

I'm with a big problem in an SSIS project.

When I execute the project in SSIS Local Machine the dtsx runs well, but when I put that in a SQL Agent Job it throws me an error:

Source: File In Folder? Description: Exception has been thrown by the target of an invocation. End Error Error: 2020-10-02 17:37:05.33 Code: 0x00000001 Source: Script Task 1 Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 17:37:01 Finished: 17:37:05 Elapsed: 3.687 seconds. The package execution failed. The step failed.

The source File In Folder is a script task, with this code:

    Dim StrFolderArrary As String()
    Dim StrFileArray As String()
    Dim fileName As String
    Dim RemoteDirectory As String

    RemoteDirectory = Dts.Variables("User::ftp_masks").Value.ToString()

    Dim cm As ConnectionManager = Dts.Connections("FTP Connection Manager") 'FTP connection manager name
    Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

    Try
        ftp.Connect() 'Connecting to FTP Server

        ftp.SetWorkingDirectory(RemoteDirectory) 'Provide the Directory on which you are working on FTP Server

        ftp.GetListing(StrFolderArrary, StrFileArray) 'Get all the files and Folders List

        'If there is no file in the folder, strFile Arry will contain nothing, so close the connection.

        If StrFileArray Is Nothing Then

            MessageBox.Show(Dts.Variables("User::FileExistsFlg").Value.ToString())

            Dts.Variables("User::FileExistsFlg").Value = 0

            ftp.Close()


            'If Files are there, Loop through the StrFileArray arrary and insert into table
        Else

            For Each fileName In StrFileArray

                MessageBox.Show(fileName)
                Dts.Variables("User::files").Value = fileName
                MessageBox.Show(Dts.Variables("User::files").Value.ToString())
                If fileName = Dts.Variables("User::files").Value.ToString() Then
                    Dts.Variables("User::FileExistsFlg").Value = 1
                    MessageBox.Show(Dts.Variables("User::FileExistsFlg").Value.ToString())
                End If
            Next
            Dts.TaskResult = ScriptResults.Success

            ftp.Close()

        End If

        MessageBox.Show("End try")

    Catch ex As Exception

        MessageBox.Show("Catch")
        Dts.Events.FireError(0, "My File Task", ex.Message, String.Empty, 0)
        MessageBox.Show("Stack Trace: " & vbCrLf & ex.StackTrace)
        Throw New ApplicationException("Something happened :(", ex)
        'Dts.TaskResult = ScriptResults.Failure
    Finally
        ' This line executes whether or not the exception occurs.
        MessageBox.Show("in Finally block")

    End Try

    '
    ' Add your code here
    '
    Dts.TaskResult = ScriptResults.Success

Can someone help me? How do I see the specific error?

Thank you for your time.

sql-server
ssis
integration
asked on Stack Overflow Oct 2, 2020 by Ricardo Gonçalves • edited Oct 2, 2020 by billinkc

1 Answer

0

One of the challenges with your code is the use of MessageBox. That is an interactive UI element. You cannot use those when an SSIS package runs in unattended mode because there would be a popup on the server, blocking all progress until someone logged in and clicked "OK". Yes, that totally happened back in the DTS days (pre 2005).

A way to keep your popups is to test for the System scoped variable of InteractiveMode. You need to check it in the ReadOnly variables list and then modify your code as

VB approximate

' This is not valid VB syntax but I can't remember their declaration/initialization sytnax
Dim interactiveMode as Boolean =  (bool) Dts.Variables["System::InteractiveMode"].Value

if interactiveMode then
    MessageBox.Show(fileName)
End If

A better way than that is to raise Information messages. Those work in both attended an unattended mode.

This is C#, you're welcome to convert to VB syntax

bool fireAgain = false;
string message = "{0}:{1}";

Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "RemoteDirectory", RemoteDirectory), string.Empty, 0, ref fireAgain);

Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "fileName", fileName), string.Empty, 0, ref fireAgain);

That would dump to the information logs the remote directory and the fileName values. In VS, that will show in the Output window as well as the Package Execution Results graphical window. On the server, that will dump to SSISDB.catalog.OperationMessages

All of that said, what else could be going on?

  • I seem to recall that the ftp client will throw an exception if the folder is empty.
  • The server could have a firewall/anti-virus blocking outbound access to port 22/23 (ftp).
  • Your instantiation of the Connection Manager could be throwing an exception
  • Same with the ftp client connection.
  • If this is not anonymous access, it could be that the server version of code is not getting credential info because it got wiped from base package when it was deployed
answered on Stack Overflow Oct 2, 2020 by billinkc

User contributions licensed under CC BY-SA 3.0