SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 How to Exit Out if No Files Exsit in FTP Folder
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

378 Posts

Posted - 12/24/2013 :  14:07:37  Show Profile  Reply with Quote
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

Edited by - jbates99 on 12/26/2013 13:43:08

jbates99
Constraint Violating Yak Guru

378 Posts

Posted - 12/26/2013 :  15:54:51  Show Profile  Reply with Quote
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

Edited by - jbates99 on 12/26/2013 15:55:23
Go to Top of Page

mandm
Yak Posting Veteran

53 Posts

Posted - 12/27/2013 :  09:58:13  Show Profile  Reply with Quote
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
Go to Top of Page

jbates99
Constraint Violating Yak Guru

378 Posts

Posted - 12/27/2013 :  17:31:30  Show Profile  Reply with Quote
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.
Go to Top of Page

mandm
Yak Posting Veteran

53 Posts

Posted - 12/27/2013 :  17:52:43  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000