The best way for a SSIS ftp task to not fail when there are no files to copy

Question

I'm using SQL Server 2005, and creating ftp tasks within SSIS.

Sometimes there will be files to ftp over, sometimes not. If there are not, I don't want the task nor the package to fail. I've changed the arrow going from the ftp task to the next to "completion", so the package runs through. I've changed the allowed number of errors to 4 (because there are 4 ftp tasks, and any of the 4 directories may or may not have files).

But, when I run the package from a job in agent, it marks the job as failing. Since this will be running every 15 minutes, I don't want a bunch of red x's in my job history, which will cause us to not see a problem when it really does occur.

How do I set the properties in the ftp task so that not finding files to ftp is not a failure? The operation I am using is "Send files".

Here is some more information: the files are on a server that I don't have any access through except ftp. And, I don't know the filenames ahead of time. The user can call them whatever they want. So I can't check for specific files, nor, I think, can I check at all. Except through using the ftp connection and tasks based upon that connection. The files are on a remote server, and I want to copy them over to my server, to get them from that remote server.

I can shell a command level ftp in a script task. Perhaps that is what I need to use instead of a ftp task. (I have changed to use the ftp command line, with a parameter file, called from a script task. It gives no errors when there are no files to get. I think this solution is going to work for me. I'm creating the parameter file dynamically, which means I don't need to have connection information in the plain text file, but rather can be stored in my configuration file, which is in a more secure location.)

-k

Answer 1

I don't have a packaged answer for you, but since no one else has posted anything yet...

You should be able to set a variable in an ActiveX script task and then use that to decide whether or not the FTP task should run. There is an example here that works with local paths. Hopefully you can adapt the concept (or if possible, map the FTP drive and do it that way).

Answer 2

Put it in a ForEach container, which iterates over the files to upload. No files, no FTP, no failure.

Answer 3

You can redirect on failure, to another task that does nothing, ie a script that just returns true.

To do this, add the new script task, highlight your FTP task, a second green connector will appear, drag this to the script task, and then double click it. Select Failure on the Value drop down. Obviously, you'll then need to handle real failures in this script task to still display right in the Job history.

Answer 4

Aha, OK - Thanks for clarification. As the FTP task cannot return a folder listing it will not be possible to use the ForEach as I initially said - That only works if you're uploading X amount of files to a remote source.

To download X amount of files, you can go two ways, either you can do it entirely in .Net in a script task, or you can populate an ArrayList with the file names from within a .Net script task, then ForEach over the ArrayList, passing the file name to a variable and downloading that variable name in a standard FTP task.

Code example to suit: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2472491&SiteID=1

So, in the above, you'd get the FileNames() and populate the ArrayList from that, then assign the ArrayList to an Object type variable in Dts.Variables, then ForEach over that Object (ArrayList) variable using code something like: http://www.sqlservercentral.com/articles/SSIS/64014/

Answer 5

(I can't accept my own answer, but this was the solution that worked for me.)

It may not be the best solution, but this works.

I use a script task, and have a bunch of variables for the ftp connection information, and source and destination directories. (Because, we'll be changing the server this is run on, and it will be easier to change in a config package.)

I create a text file on the fly, and write the ftp commands to it:

    Dim ftpStream As StreamWriter = ftpFile.CreateText()
    ftpStream.WriteLine(ftpUser)
    ftpStream.WriteLine(ftpPassword)
    ftpStream.WriteLine("prompt off")
    ftpStream.WriteLine("binary")
    ftpStream.WriteLine("cd " & ftpDestDir)
    ftpStream.WriteLine("mput " & ftpSourceDir)
    ftpStream.WriteLine("quit 130")
    ftpStream.Close()

Then, after giving it enough time to really close, I start a process to do the ftp command:

    ftpParameters = "-s:" & ftpParameterLoc & ftpParameterFile & " " & ftpServer
    proc = System.Diagnostics.Process.Start("ftp", ftpParameters)

Then, after giving it some more time for the ftp process to run, I delete the temporary ftp file (that has connection information in it!).

If files don't exist in the source directory (the variable has the \\drive\dir\*.* mapping), then there is no error. If some other error happens, the task still fails, as it should.

I'm new to SSIS, and this may be a kludge. But it works for now. I guess I asked for the best way, and I'll certainly not claim that this is it.

As I pointed out, I have no way of knowing what the files are named, or even if there are any files there at all. If they are there, I want to get them.

-k

Answer 6

See

Here

I had almost the same problem but, with retrieving files. I wanted the package NOT to fail when no files were found on FTP server. The above link stops the error bubbling up and causing the package to fail; something you would have thought FailPackageOnError=false should have done? :-S

Hope this solves it for you too!

Rich

Answer 7

You can use the free SSIS FTP Task++ from eaSkills. It doesn't throw an error if the file or files don't exist, it support wild cards and gives you the option to download and delete if you need to do so.

Here's the link to the feature page: http://www.easkills.com/ssis/ftptask