How to Exit Out if No Files Exsit in FTP Folder

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to Exit Out if No Files Exsit in FTP Folder

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-12-24 : 14:07:37

I have devloped a VB Script task which I no longer understand, lol.

I know there are no files at the ftp site (confirmed)
Yet the ForEach Filename loop executes 1 time and increments my filectr value.

Can anyone explain why this happens, please? Thanks

Public Sub Main()
On Error Resume Next
Dim conn As ConnectionManager
Dim ftp_client As FtpClientConnection
Dim sFolderNames() As String = Nothing
Dim iFile As String
Dim sFileNames() As String = Nothing
Dim FileCtr As Integer = 0
Dim sFileName(0) As String

conn = Dts.Connections("FTPDSSI")
ftp_client = New FtpClientConnection(conn.AcquireConnection(Nothing))
ftp_client.Connect()
ftp_client.SetWorkingDirectory("/In/Transaction")

ftp_client.GetListing(sFolderNames, sFileNames)
Dts.Variables("FileCountTotal").Value = 0
For Each fileName As String In sFileNames
MsgBox(fileName)
sFileName(0) = fileName
If fileName.EndsWith("txt", StringComparison.OrdinalIgnoreCase) And fileName.StartsWith("CVD_invout", StringComparison.OrdinalIgnoreCase) Then
ftp_client.ReceiveFiles(sFileName, "\\DynamicsTestApp\C$\Program Files (x86)\Microsoft Dynamics\DSSI\APFilesPending\", True, False)

FileCtr = FileCtr + 1
MsgBox("AddedOne")
'MsgBox(fileName) shows blank

End If
iFile = sFileName(0)

Next fileName
ftp_client.Close()

Dts.Variables("FileCountTotal").Value = FileCtr
MsgBox(FileCtr)
Dts.TaskResult = ScriptResults.Success
End Sub

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-12-26 : 15:54:51

Thru some trial-and-error testing, I have determined that the "ForEach FileName" block executes 1 time, adding 1 to filectr, whether there are no files in the ftp folder or 1 file.
When there ar eno files to download, the MsgBox(fileName) shows blank... so how does it get into the If fileName.EndsWith section? It goes into that block of code
even when there are no files at the ftp site.

Frustrating. I was hoping this technique could be used to determine if files exist for downloading.
Any ideas? Thanks

mandm
Posting Yak Master

120 Posts

Posted - 2013-12-27 : 09:58:13

What is the value in filename before you assign it to sFileName? If it is NULL then that could be tripping up your equality checks and always returning true. See the link below.

http://msdn.microsoft.com/en-us/library/zbchw6hz(v=vs.85).aspx

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-12-27 : 17:31:30

Thanks for your reply, mandm. filename displays as blank in a Msgbox.
But I found another way. I just added On Error Resume Next to my VBScript task... to keep it from crashing when no files exist.
Then added a simple VBScript task to check to # of files in the folder that gets downloaded to. And used precedence constraints to a) send an email if no files were downloaded or b) continue processing with the next task.
Probably not the best technique but I'll go with it for now.

mandm
Posting Yak Master

120 Posts

Posted - 2013-12-27 : 17:52:43

Another way that we've used in the past is to separate out the FTP task and pull down the files (if there are any). Then load a variable with the count of the files received. You can then use the variable to branch and go into the script task or bypass it if there are no files. Sounds like you have something workable though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources