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:
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:
Any help would be appreciated, thanks.
rp
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
