How to avoid SSIS FTP task from failing when there

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 no files, 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.)

Answer 1

Check this link that describes about gracefully handling task error in SSIS Package.

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!

Answer 2

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 3

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

Answer 4

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

Answer 5

An alternative is to use this FTP File Enumerator

Answer 6

This is another solution that is working for me, using built-in stuff and so without manually re-writing the FTP logic:

1) Create a variable in your package called FTP_Error

2) Click your FTP Task, then click "Event Handlers" tab

3) Click within the page to create an event handler for "FTP Task/OnError" - this will fire whenever there is trouble with the FTP

4) From the toolbox, drag in a Script Task item, and double-click to open that up

5) In the first pop-up, ReadOnlyVariables - add System::ErrorCode, System::ErrorDescription

6) In the first pop-up, ReadWriteVariables - add your User::FTP_Error variable

7) Edit Script

8) In the script set your FTP_Error variable to hold the ReadOnlyVariables we had above:

Dts.Variables["FTP_Error"].Value = "ErrorCode:" + Dts.Variables["ErrorCode"].Value.ToString() + ", ErrorDescription=" + Dts.Variables["ErrorDescription"].Value.ToString();

9) Save and close script

10) Hit "OK" to script task

11) Go back to "Control Flow" tab

12) From the FTP task, OnError go to a new Script task, and edit that

13) ReadOnlyVariables: User::FTP_Error from before

14) Now, when there are no files found on the FTP, the error code is -1073573501 (you can find the error code reference list here: http://msdn.microsoft.com/en-us/library/ms345164.aspx)

15) In your script, put in the logic to do what you want - if you find a "no files found" code, then maybe you say task successful. If not, then task failed. And your normal flow can handle this as you wish:

if (Dts.Variables["FTP_Error"].Value.ToString().Contains("-1073573501"))
{
  // file not found - not a problem
  Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
  // some other error - raise alarm!
  Dts.TaskResult = (int)ScriptResults.Failure;
}

And from there your Succeeded/Failed flow will do what you want to do with it.