March 16, 2010 at 4:17 am
Hi all!
Is it possible to check for the existence of a folder or a file in a FTP server?
I tried many times, but with no luck...
Do you have a solution?
Thanks!
March 16, 2010 at 9:17 am
I use the below script to check file existence on a FTP server.. This should work for you..It uses two variables FileExists and FTPPwd... after the Script task use FileExists in Precedence Constraint and based on the File Existence you can design the rest of the workflow
(Reference:MSDN)
Dim RemoteFile As IO.FileInfo()
Public Sub Main()
'
' Add your code here
'
Dim ftpConnectionManager As ConnectionManager
ftpConnectionManager = Dts.Connections("FTP Connection Manager")
Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FTPPwd").Value)
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "xxxxxxxxx")
cm.Properties("ServerUserName").SetValue(cm, "xxxxxxxxx")
cm.Properties("ServerPassword").SetValue(cm, "xxxxxxxxx")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
'Get file listing
Dim fileNames() As String
Dim folderNames() As String
http://ftp.GetListing(folderNames, fileNames)
If fileNames.Length >= 1 Then
Dts.Variables("FileExists").Value = True
Else
Dts.Variables("FileExists").Value = False
End If
Catch ex As Exception
'Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
March 16, 2010 at 9:17 am
You've tried.. Ok, using what methods? I think we need a bit more detail..
CEWII
March 16, 2010 at 4:01 pm
Thanks, I found a solution through your help. I used a variable to store the folder and file names and the getListing method to match the names a nd discover if a file/folder is present or not!
March 25, 2010 at 5:21 am
Hi all! I've found a problem in my code that I'm not able to understand...
Here is the snippet..it seems that Array.IndexOf breaks the execution of the snippet whenever it does not find the string to be matched...
Where am I wrong?
Thanks!
Public Class ScriptMain
Public Sub Main()
Try
'Create the connection to the ftp server
Dim oleDbAdapter As New Data.OleDb.OleDbDataAdapter
Dim companyTable As New DataTable
Dim filesTable As New DataTable
Dim connManager As ConnectionManager = Dts.Connections.Add("FTP")
Dim ftpConn As FtpClientConnection
Dim strFolders As String()
Dim strFiles(0) As String
Dim fileName As String
Dim filePath As String
Dim localPath As String
Dim folderName As String
Dim rowFolder As Data.DataRow
Dim rowFile As Data.DataRow
Dim FTPRoot As String
Dim localRoot As String
Dim localSubmRoot As String
Dim folderNames As String()
Dim fileNames As String()
'Retrieve Object Variable Values
oleDbAdapter.Fill(companyTable, Dts.Variables("CompanyNames").Value)
oleDbAdapter.Fill(filesTable, Dts.Variables("FileNames").Value)
'Retrieve Other useful Variables
FTPRoot = Dts.Variables("FTPSourceRootDirPath").Value.ToString()
localRoot = Dts.Variables("LocalIncomingRootDirPath").Value.ToString
'Set the properties like username & password
connManager.Properties("ServerName").SetValue(connManager, Dts.Variables("FTPServerName").Value.ToString)
connManager.Properties("ServerUserName").SetValue(connManager, Dts.Variables("FTPUserName").Value.ToString)
connManager.Properties("ServerPassword").SetValue(connManager, Dts.Variables("FTPPassword").Value.ToString)
connManager.Properties("ServerPort").SetValue(connManager, "21")
connManager.Properties("Timeout").SetValue(connManager, "5") 'The 0 setting will make it not timeout
connManager.Properties("ChunkSize").SetValue(connManager, "500") '1000 kb
connManager.Properties("Retries").SetValue(connManager, "1")
'Open The Connection
ftpConn = New FtpClientConnection(connManager.AcquireConnection(Nothing))
If Not (DirectoryExists(localRoot)) Then
CreateDirectory(localRoot)
End If
Dim success As Boolean = ftpConn.Connect()
If (success) Then
ftpConn.SetWorkingDirectory(FTPRoot)
ftpConn.GetListing(folderNames, fileNames)
For Each rowFolder In companyTable.Rows
folderName = rowFolder.Item(1).ToString
' If Remote Folder is to be moved
If (Array.IndexOf(folderNames, folderName) <> -1) Then
'Set the Current Dir
ftpConn.SetWorkingDirectory(FTPRoot & folderName)
localPath = localRoot & folderName
'If Dir does not exist, create it
If Not (DirectoryExists(localPath)) Then
CreateDirectory(localPath)
End If
'Get all files in the directory
ftpConn.GetListing(strFolders, fileNames)
For Each rowFile In filesTable.Rows
fileName = rowFile.Item(0).ToString
' If Remote File is to be moved
System.Windows.Forms.MessageBox.Show(fileName)
Dim cont As Integer = Array.IndexOf(fileNames, fileName)
If (cont <> -1) Then
'Receive it
strFiles(0) = fileName
ftpConn.ReceiveFiles(strFiles, localPath, True, False)
'If the new file has been created
If VerifyFiles(fileName, localPath) Then
'Delete it
ftpConn.DeleteFiles(strFiles)
End If
End If
Next
End If
Next
ftpConn.Close()
Else
Dts.TaskResult = Dts.Results.Failure
End If
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
March 25, 2010 at 8:31 am
So does it cause an exception? If so if it causes a specific one you could use a try/catch block to handle he exception and keep going..
CEWII
March 25, 2010 at 9:25 am
unfortunately, it does not!
In fact, if an exception was raised, the task should have been in error, while it ends correctly!
March 25, 2010 at 10:42 am
So what does it return when it "errors"? It seems like you need to test for that.
CEWII
March 25, 2010 at 3:46 pm
Elliott, I tried to debug the script, but it seems that once reached the Array.IndexOf method, the script exits without error...it a really strange behaviour I cannot explain..thus I asked your help 🙁
March 25, 2010 at 5:53 pm
I think this is your problem
End If
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
Let me explain what is happening. You get an exception and it sets the task result to fail but then immediately gets out of the try/catch block and sets it to success.. Try this:
End If
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireInformation(0, String.Empty, ex.ToString(), String.Empty, 0, True)
Dts.TaskResult = Dts.Results.Failure
End Try
CEWII
March 26, 2010 at 3:05 am
You are absolutely right...
It was an obvious thing...thanks!
March 26, 2010 at 7:30 am
Extra pair of eyes...
You are very welcome.
CEWII
March 26, 2010 at 8:21 am
I found this. Useful whilst waiting for a file to ftp or download.
http://www.konesans.com/filewatcher.aspx
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply