May 13, 2008 at 1:16 pm
I have a working package that gets some XML files out of a FTP folder, pulls them down local, processes them and then deletes the original from the FTP source.
The plan is for the package to run every hour to check for recent activity.
However, when there are no files in the FTP folder, my get files step errors. Is there a way to check the FTP folder first for existing files prior to doing the get step?
Thanks in advance
May 14, 2008 at 6:07 am
Hi
In SSIS FTP Task, we don't have file operation "File Exists". Instead you can set the Maximum error count at package to a huge number so that if the file doesnot exist in FTP site wouldn't throw any error.
Or you can create VB script with FileSystemOBject to check the file existance.
Hope this helps you
Regards
Bindu
May 14, 2008 at 8:53 am
I tried that.....not sure if it will work once compiled and scheduled but definitely was not working within the Designer.
What I ended up doing was I replaced my FTP ReceiveFiles task with a Script Task with the following code:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
Dim strFolders As String()
Dim strFiles As String()
Dim fileCount As Int32
fileCount = 0
Dim fileName As String
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.someserver.com
cm.Properties("ServerUserName").SetValue(cm, "someuser")
cm.Properties("ServerPassword").SetValue(cm, "somepass")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
http://ftp.SetWorkingDirectory("RemoteFolder/SubFoder")
http://ftp.GetListing(strFolders, strFiles)
For Each fileName In strFiles
fileCount = fileCount + 1
Next
http://ftp.ReceiveFiles(strFiles, "LocalDrive:\LocalFolder\LocalSub",True, False)
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Note: Not my code.....found it online and modified it slightly to my needs.
I'm counting the total of files but don't actually do anything with the count but I left it in there just in case someone is trying to do something similar and either wants to report back to a variable the number of files transmitted or only do the transfer when there's a certain number of files.
Cheers!
May 15, 2008 at 5:33 am
Have you tried using the WMI Event Watcher Task? This should give you the behavior you're looking for. There are alos 3rd party vendors that have developed task for just such a task.
Dave
November 4, 2008 at 10:34 am
This code works great. But there are a few adjustments I need. But I cannot figure out how to do that.
🙂
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
Dim strFolders As String()
Dim strFiles As String()
Dim fileCount As Int32
fileCount = 0
Dim fileName As String
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.someserver.com
cm.Properties("ServerUserName").SetValue(cm, "someuser")
cm.Properties("ServerPassword").SetValue(cm, "somepass")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
http://ftp.SetWorkingDirectory("RemoteFolder/SubFoder")
http://ftp.GetListing(strFolders, strFiles)
For Each fileName In strFiles
if fileName.Contains("1104") Then
' This returns all the files in the remote directory
' I only want to see the filenames that contain "1104" IN THE NAME.
' wOULD REALLY LIKE TO SEE ONLY THE FILES THAT WERE MODIFIED 'ON A SPECIFIC DATE
http://ftp.ReceiveFiles (strFiles, "FileGOESHERE",True, False)
fileCount = fileCount + 1
end if
Next
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
February 3, 2009 at 7:14 am
thanks a lot it was really a good solution for my requirement also.I appreciate for ur efforts to post in this blog.
🙂
July 21, 2010 at 4:38 pm
Sorry this is late reply but someone may use this in the future.. Simple solution is use normal FTP Script Task and in the general tab when you click edit, set StopOnFailure to be False
July 22, 2010 at 8:23 am
Or you could Add a Precedence Constraint that on Failure branches your flow to harmless tasks.
Steve
February 20, 2012 at 11:35 pm
steve block (7/22/2010)
Or you could Add a Precedence Constraint that on Failure branches your flow to harmless tasks.Steve
It doesn't work for me. I checked StopOnFailure = False. FTP task becomes red and even goes to the harmless task which is Script task and stops, no next loop inside Foreach Loop Container. If I set MaximumErrorCount > 1 then it goes to success arrow but I need to go on failure and then make next loop. Please, advice.
February 21, 2012 at 12:04 am
Seems I was able to figure out the problem. Added MaximumErrorCount = 100 for Loop Container and it doesn't stop now.
June 8, 2015 at 9:04 am
I have another solution, create a bat file and put on it lines below to kill the process :
echo off
taskkill /F /IM http://ftp.exe /T
After that put a timeout to your previous task (ftp task / execute sql task....etc) per exemple (30 or 60 sec) and add a Precedence Constraint that on Failure branches your flow to the taskkill bat.
Hope it helps !
May 31, 2019 at 1:10 pm
thomas.mohler - thank you for posting a simple and effective solution - worked great!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply