FTP Script NOT failing when no file found.

• below86

  SSChampion

  Points: 11513

  More actions

  May 21, 2018 at 9:38 am

  #411523

  I have an SSIS package that is running a VB script task to FTP a file from the FTP server to our local server.  The package is NOT failing when the file is not on the FTP server.  The 'FailPackageOnFailure' is set to True, I even set the 'FailParentOnFailure' to True, still the job does NOT fail when the files does not exists on the FTP server.  We are using WinSCP to FTP the file.
  Here is a portion on the VB script task to 'GET' the file.
    ' Execute Get command for premium data file
   winscp.StandardInput.WriteLine("Get " & Dts.Variables("InputPremiumFile").Value.ToString & _
                " " & Dts.Variables("OutputPremiumPath").Value.ToString)

     'Close connection
   winscp.StandardInput.Close()

     'Wait until winSCP finishes
   winscp.WaitForExit()

     'Check for success of ftp
   If winscp.ExitCode = 0 Or winscp.ExitCode = 1 Then
    Console.WriteLine("FTP process successful! FTP log file located at: " & Dts.Variables("FtpLogPath").Value.ToString)
    Dts.TaskResult = ScriptResults.Success
   Else
    'Create a List collection to store contents of StandardOutput
    Dim output As New List(Of String)
    While Not myStreamReader.EndOfStream
      output.Add(myStreamReader.ReadLine())
    End While

      '' Write final 10 lines of stream buffer to the console
    '' so it is displayed in the Job Log Viewer
    For i As Integer = output.Count - 10 To output.Count - 1
      Console.WriteLine("FTP ERROR: " & output(i))
    Next

      Console.WriteLine("Error during FTP. FTP log file located at: " & Dts.Variables("FtpLogPath").Value.ToString)
    Dts.TaskResult = ScriptResults.Failure
   End If

  Here is what I get from the FTP log file:
  < 2018-05-21 09:38:11.102 Script: cd RECEIVE
  . 2018-05-21 09:38:11.102 Cached directory change via "RECEIVE" to "/RECEIVE".
  . 2018-05-21 09:38:11.102 Getting current directory name.
  < 2018-05-21 09:38:11.102 Script: /RECEIVE
  > 2018-05-21 09:38:11.102 Script: Get Premium.txt \\ssisdev\Data\Premium.txt
  < 2018-05-21 09:38:11.102 Script: Get Premium.txt \\ssisdev\Data\Premium.txt
  . 2018-05-21 09:38:11.102 Listing file "Premium.txt".
  > 2018-05-21 09:38:11.102 Type: SSH_FXP_LSTAT, Size: 40, Number: 263
  < 2018-05-21 09:38:11.164 Type: SSH_FXP_STATUS, Size: 70, Number: 263
  < 2018-05-21 09:38:11.164 Status code: 2, Message: 263, Server: Cannot find message [/RECEIVE/Premium.txt], Language:
  < 2018-05-21 09:38:11.164 Script: Can't get attributes of file 'Premium.txt'.
  < 2018-05-21 09:38:11.164 Script: No such file or directory.
  < 2018-05-21 09:38:11.164 Error code: 2
  < 2018-05-21 09:38:11.164 Error message from server: Cannot find message [/RECEIVE/Premium.txt]
  < 2018-05-21 09:38:11.164 Request code: 7
  . 2018-05-21 09:38:11.164 Script: Failed
  . 2018-05-21 09:38:11.164 Script: Exit code: 1
  . 2018-05-21 09:38:11.164 Closing connection.
  . 2018-05-21 09:38:11.164 Sending special code: 12
  . 2018-05-21 09:38:11.164 Sent EOF message

  Can anyone explain why this is NOT failing?

  This is causing an issue in which when the package does not fail when the file is not on the FTP site it picks up the file processed in the previous run.  This is either causing duplicate data to be entered or it fails for duplicate keys later on.
  I could set up a step at the beginning of the package to delete the prior file before trying to pull it from the FTP site.  This would fix my duplicate data or duplicate keys issues.  But I would really like to understand why this does not fail.

  Let me know if you need more detail.  And thanks in advance for your help.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.