Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

martin

Re: Recursively Check sFTP folders

So, is the problem that you do not know how to catch exceptions in PowerShell?
For that see any WinSCP .NET example for PowerShell.
E.g. https://winscp.net/eng/docs/library#powershell
rpeare

Recursively Check sFTP folders

Hello, I'm in the process of writing a powershell script that recursively checks all folders on a sFTP site. In the process of doing this I write the folder names to a SQL log. This part is working fine except for one problem. I am not able to capture (I don't know how to capture) the error that occurs when I do not have access to a particular folder. The error message generated is:

Exception calling "ListDirectory" with "1" argument(s): "Error listing directory '/Folder1/Folder2/Folder3/Folder4'."
At C:\Documents and Settings\rpeare.PPSDOM\Local Settings\Temp\df054dc2-58bc-4922-b02f-c9c3b04d22c0.ps1:90 char:44
+ $directory3 = $session.ListDirectory <<<< ("$FTP_RemoteBaseDir$Level1Dir/$Level2Dir")
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException


What I'd like to be able to do is capture this error and if it occurs mark an additional field in my SQL table indicating it's a restricted access folder.

Is there a way to capture the error WITHOUT terminating my script, I just want it to go on to trying the next folder.

Here is my current script:

cls

#**************************** START OPEN CONNECTION TO SQL DB **********************************
$ConnString = "Data Source=<Server Name>; Initial Catalog=<Database Name>; Integrated Security=SSPI"
$adOpenStatic = 3
$adLockOptimistic = 3
$objSecConn = New-Object -comobject ADODB.Connection      #connection to the SQL database
$objSecConn.Open("Provider=SQLOLEDB; $ConnString")
#**************************** END   OPEN CONNECTION TO SQL DB **********************************

# Load WinSCP .NET assembly
[Reflection.Assembly]::LoadFrom("c:\program files\winscp\WinSCP.dll") | Out-Null
   
#*********************  START Get Strings used in the remainder of the script *********************************
$RSStrings = New-Object -ComObject ADODB.Recordset
$RSStrings.Open("PS_Strings", $objSecConn, $adOpenStatic, $adLockOptimistic)
         
$FTP_Server = $RSStrings.Fields.Item("FTP_Address").value
$FTP_User = $RSStrings.Fields.Item("FTP_User").value
$FTP_Pass = $RSStrings.Fields.Item("FTP_Password").value
$FTP_FingerPrint = $RSStrings.Fields.Item("FTP_FingerPrint").value
   
$FTP_BaseDir = $RSStrings.Fields.Item("Server_Base_Path").value
$FTP_RootDir = $RSStrings.Fields.Item("FTP_LocalRootFolder").value
$FTP_RootDir = "$FTP_BaseDir$FTP_RootDir"
$FTP_RemoteBaseDir = $RSStrings.Fields.Item("FTP_RemoteRootFolder").value   
$FM_ConnStr = $RSStrings.Fields.Item("File_Maintenance_Connection_String").value   
#*********************  END   Get Strings used in the remainder of the script *********************************

# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions
$sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
$sessionOptions.HostName = $FTP_Server
$sessionOptions.UserName = $FTP_User
$sessionOptions.Password = $FTP_Pass
$sessionOptions.SshHostKeyFingerprint = $FTP_FingerPrint
$session = New-Object WinSCP.Session

$session.Open($sessionOptions)
         
$directory = $session.ListDirectory($FTP_RemoteBaseDir)
foreach ($fileInfo in $directory.Files)
   {
   if ($fileInfo.IsDirectory -eq $true)
      {
      $Level1Dir = $fileInfo.name
      if ($Level1Dir -ne "." -and $Level1Dir -ne "..")
         {
         $query = "SELECT * FROM PS_FTPFolders WHERE ((Directory1) = '$level1Dir' AND (Directory2) Is Null)"
         $objFC = New-Object -comobject ADODB.Recordset
         $objFC.Open($query, $objSecConn, $adOpenStatic, $adLockOptimistic)
         if ($objfc.absoluteposition -eq "adposunknown")
            {
            $objAddConn = New-Object System.Data.SqlClient.SqlConnection($ConnString)
            $objAddConn.Open()
            $cmd = $objAddConn.CreateCommand()
            $cmd.CommandText ="INSERT PS_FTPFolders (Directory1) VALUES ('$Level1Dir')"
            $cmd.ExecuteNonQuery()
            $objAddConn.Close()
            }
         $objFC.Close()
         $directory2 = $session.ListDirectory("$FTP_RemoteBaseDir$Level1Dir")
         foreach ($fileInfo in $directory2.Files)
            {
            if ($fileInfo.IsDirectory -eq $true)
               {
               $Level2Dir = $fileInfo.name
               if ($Level2Dir -ne "." -and $Level2Dir -ne "..")
                  {
                  $query = "SELECT * FROM PS_FTPFolders WHERE ((Directory1) = '$level1Dir' AND (Directory2) = '$level2dir' and (Directory3) Is Null)"
                  $objFC = New-Object -comobject ADODB.Recordset
                  $objFC.Open($query, $objSecConn, $adOpenStatic, $adLockOptimistic)
                  if ($objfc.absoluteposition -eq "adposunknown")
                     {
                     $objAddConn = New-Object System.Data.SqlClient.SqlConnection($ConnString)
                     $objAddConn.Open()
                     $cmd = $objAddConn.CreateCommand()
                     $cmd.CommandText ="INSERT PS_FTPFolders (Directory1, Directory2) VALUES ('$Level1Dir', '$Level2Dir')"
                     $cmd.ExecuteNonQuery()
                     $objAddConn.Close()
                     }
                  $objFC.Close()
                  $directory3 = $session.ListDirectory("$FTP_RemoteBaseDir$Level1Dir/$Level2Dir")
                  foreach ($fileInfo in $directory3.Files)
                     {
                     if ($fileInfo.IsDirectory -eq $true)
                        {
                        $Level3Dir = $fileInfo.name
                        if ($Level3Dir -ne "." -and $Level3Dir -ne "..")
                           {
                           $query = "SELECT * FROM PS_FTPFolders WHERE ((Directory1) = '$level1Dir' AND (Directory2) = '$level2dir' and (Directory3) = '$Level3Dir' and (Directory4) Is Null)"
                           $objFC = New-Object -comobject ADODB.Recordset
                           $objFC.Open($query, $objSecConn, $adOpenStatic, $adLockOptimistic)
                           if ($objfc.absoluteposition -eq "adposunknown")
                              {
                              $objAddConn = New-Object System.Data.SqlClient.SqlConnection($ConnString)
                              $objAddConn.Open()
                              $cmd = $objAddConn.CreateCommand()
                              $cmd.CommandText ="INSERT PS_FTPFolders (Directory1, Directory2, Directory3) VALUES ('$Level1Dir', '$Level2Dir', '$Level3Dir')"
                              $cmd.ExecuteNonQuery()
                              $objAddConn.Close()
                              }
                           $objFC.Close()
                           }   
                        }   
                     }
                  }         
               }                     
            }   
         }   
      }
   }   
$objSecConn.close()   


Any help would be appreciated, thanks.
rp