如何避免SSIS FTP任务在没有文件下载时失败?

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

我正在使用SQL Server 2005，并在SSIS中创建ftp任务。

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).

有时会有文件传送到ftp，有时不会。如果没有文件，我不希望任务或包失败。我将箭头从ftp任务改为“完成”，这样包就可以运行了。我将允许的错误数量更改为4(因为有4个ftp任务，4个目录中的任何一个都可能有或没有文件)。

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.

但是，当我从代理中的作业运行包时，它将该作业标记为失败。因为它将每15分钟运行一次，我不想在我的工作历史中出现一串红色的x，这将导致我们在问题真正发生时看不到它。

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".

如何在ftp任务中设置属性，使查找到ftp的文件不会失败?我正在使用的操作是“发送文件”。

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.

这里有更多的信息:文件在服务器上，除了ftp我没有任何访问权限。而且，我事先不知道文件名。用户可以任意调用它们。所以我不能检查特定的文件，我想，我也不能检查。除非使用基于该连接的ftp连接和任务。这些文件在远程服务器上，我想将它们复制到我的服务器上，以便从远程服务器获取它们。

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.)

我可以在脚本任务中shell命令级ftp。也许这就是我需要使用的，而不是ftp任务。(我已经改变了使用ftp命令行，使用一个参数文件，从脚本任务调用。当没有文件需要获取时，它不会产生错误。我认为这个解决方案对我有用。我正在动态地创建参数文件，这意味着我不需要在纯文本文件中拥有连接信息，而是可以存储在我的配置文件中，该文件位于一个更安全的位置。

12 个解决方案

#1

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

检查这个描述在SSIS包中优雅地处理任务错误的链接。

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

我遇到了几乎相同的问题，但是，通过检索文件。我希望当FTP服务器上没有找到文件时，这个包不会失败。上面的链接阻止错误冒泡并导致包失败;你认为FailPackageOnError=false应该做的事情?:- s

Hope this solves it for you too!

希望这也能解决你的问题!

#2

I understand that you have found an answer to your question. This is for other users who might stumble upon this question. Here is one possible way of achieving this. Script Task can be used to find the list of files present in an FTP folder path for a given pattern (say *.txt). Below example shows how this can be done.

我知道你已经找到了你问题的答案。这是为其他可能偶然发现这个问题的用户准备的。这里有一种可能的实现方法。脚本任务可用于查找给定模式(如*.txt)的FTP文件夹路径中存在的文件列表。下面的示例展示了如何实现这一点。

Step-by-step process:

循序渐进的过程:

1. On the SSIS package, create an FTP Connection named FTP and also create 5 variables as shown in screenshot #1. Variable RemotePath contains the FTP folder path; LocalPath contains the folder where the files will be downloaed to; FilePattern contains the file pattern to find the list of files to download from FTP server; FileName will be populated by the Foreach loop container but to avoid FTP task design time error, it can be populated with / or the DelayValidation property on the FTP Task can be set to True.

   在SSIS包中，创建一个名为FTP的FTP连接，并创建5个变量，如屏幕截图#1所示。变量RemotePath包含FTP文件夹路径;LocalPath包含文件向下loaed到的文件夹;FilePattern包含查找要从FTP服务器下载的文件列表的文件模式;文件名将由Foreach循环容器填充，但是为了避免FTP任务设计时间错误，可以使用/或将FTP任务上的DelayValidation属性设置为True。

2. On the SSIS package, place a Script Task, Foreach Loop container and FTP Task within the Foreach Loop container as shown in screenshots #2.

   在SSIS包中，在Foreach循环容器中放置一个脚本任务、Foreach循环容器和FTP任务，如屏幕截图#2所示。

3. Replace the Main() method within the Script Task with the code under the Script Task Code section. Script Task will populate the variable ListOfFiles with the collection of files matching a given pattern. This example will first use the pattern *.txt, which yields no results and then later the pattern *.xls that will match few files on the FTP server.

   将脚本任务中的Main()方法替换为脚本任务代码部分下的代码。脚本任务将使用与给定模式匹配的文件集合填充变量ListOfFiles。本例将首先使用模式*。txt，它不会产生任何结果，然后是模式*。xls将匹配FTP服务器上的几个文件。

4. Configure the Foreach Loop container as shown in screenshots #3 and #4. This task will loop through the variable **ListOfFiles*. If there are no files, the FTP task inside the loop container will not execute. If there are files, the FTP task inside the loop container will execute for the task for the number of files found on the FTP server.

   配置Foreach循环容器，如屏幕截图#3和#4所示。此任务将循环遍历变量**ListOfFiles*。如果没有文件，则循环容器内的FTP任务将不会执行。如果存在文件，则循环容器内的FTP任务将执行用于FTP服务器上的文件数量的任务。

5. Configure the FTP Task as shown in screenshots #5 and #6.

   配置FTP任务，如屏幕截图#5和#6所示。

6. Screenshot #7 shows sample package execution when no matching files are found for the pattern *.txt.

   当没有为模式*.txt找到匹配的文件时，屏幕截图#7显示了示例包的执行。

7. Screenshot #8 shows the contents of the folder C:\temp\ before execution of the package.

   屏幕截图#8显示了文件夹C:\temp\在执行包之前的内容。

8. Screenshot #9 shows sample package execution when matching files are found for the pattern *.xls.

   屏幕截图#9显示了在为模式*.xls找到匹配文件时执行的示例包。

9. Screenshot #10 shows the contents of the FTP remote path /Practice/Directory_New.

   屏幕截图#10显示了FTP远程路径/实践/Directory_New的内容。

10. Screenshot #11 shows the contents of the folder C:\temp\ after execution of the package.

    屏幕截图#11显示了执行包后文件夹C:\temp\的内容。

11. Screenshot #12 shows the package failure when provided with incorrect Remote path.

    屏幕截图#12显示当提供错误的远程路径时包失败。

12. Screenshot #13 shows the error message related to the package failure.

    屏幕截图#13显示了与包失败相关的错误消息。

Hope that helps.

希望有帮助。

Script Task Code:

脚本任务代码:

C# code that can be used in SSIS 2008 and above.

c#代码可以在SSIS 2008和以上版本中使用。

Include the using statement using System.Text.RegularExpressions;

包含使用System.Text.RegularExpressions的using语句;

public void Main()
{
    Variables varCollection = null;
    ConnectionManager ftpManager = null;
    FtpClientConnection ftpConnection = null;
    string[] fileNames = null;
    string[] folderNames = null;
    System.Collections.ArrayList listOfFiles = null;
    string remotePath = string.Empty;
    string filePattern = string.Empty;
    Regex regexp;
    int counter;

    Dts.VariableDispenser.LockForWrite("User::RemotePath");
    Dts.VariableDispenser.LockForWrite("User::FilePattern");
    Dts.VariableDispenser.LockForWrite("User::ListOfFiles");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    try
    {
        remotePath = varCollection["User::RemotePath"].Value.ToString();
        filePattern = varCollection["User::FilePattern"].Value.ToString();

        ftpManager = Dts.Connections["FTP"];
        ftpConnection = new FtpClientConnection(ftpManager.AcquireConnection(null));
        ftpConnection.Connect();
        ftpConnection.SetWorkingDirectory(remotePath);
        ftpConnection.GetListing(out folderNames, out fileNames);
        ftpConnection.Close();

        listOfFiles = new System.Collections.ArrayList();
        if (fileNames != null)
        {
            regexp = new Regex("^" + filePattern + "$");
            for (counter = 0; counter <= fileNames.GetUpperBound(0); counter++)
            {
                if (regexp.IsMatch(fileNames[counter]))
                {
                    listOfFiles.Add(remotePath + fileNames[counter]);
                }
            }
        }

        varCollection["User::ListOfFiles"].Value = listOfFiles;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(-1, string.Empty, ex.ToString(), string.Empty, 0);
        Dts.TaskResult = (int) ScriptResults.Failure;
    }
    finally
    {
        varCollection.Unlock();
        ftpConnection = null;
        ftpManager = null;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

VB code that can be used in SSIS 2005 and above.

可以在SSIS 2005和以上版本中使用的VB代码。

Include the Imports statement Imports System.Text.RegularExpressions

包括导入语句导入system . text .正则表达式。

Public Sub Main()
    Dim varCollection As Variables = Nothing
    Dim ftpManager As ConnectionManager = Nothing
    Dim ftpConnection As FtpClientConnection = Nothing
    Dim fileNames() As String = Nothing
    Dim folderNames() As String = Nothing
    Dim listOfFiles As Collections.ArrayList
    Dim remotePath As String = String.Empty
    Dim filePattern As String = String.Empty
    Dim regexp As Regex
    Dim counter As Integer

    Dts.VariableDispenser.LockForRead("User::RemotePath")
    Dts.VariableDispenser.LockForRead("User::FilePattern")
    Dts.VariableDispenser.LockForWrite("User::ListOfFiles")
    Dts.VariableDispenser.GetVariables(varCollection)

    Try

        remotePath = varCollection("User::RemotePath").Value.ToString()
        filePattern = varCollection("User::FilePattern").Value.ToString()

        ftpManager = Dts.Connections("FTP")
        ftpConnection = New FtpClientConnection(ftpManager.AcquireConnection(Nothing))

        ftpConnection.Connect()
        ftpConnection.SetWorkingDirectory(remotePath)
        ftpConnection.GetListing(folderNames, fileNames)
        ftpConnection.Close()

        listOfFiles = New Collections.ArrayList()
        If fileNames IsNot Nothing Then
            regexp = New Regex("^" & filePattern & "$")
            For counter = 0 To fileNames.GetUpperBound(0)
                If regexp.IsMatch(fileNames(counter)) Then
                    listOfFiles.Add(remotePath & fileNames(counter))
                End If
            Next counter
        End If

        varCollection("User::ListOfFiles").Value = listOfFiles

        Dts.TaskResult = ScriptResults.Success

    Catch ex As Exception
        Dts.Events.FireError(-1, String.Empty, ex.ToString(), String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
    Finally
        varCollection.Unlock()
        ftpConnection = Nothing
        ftpManager = Nothing
    End Try

    Dts.TaskResult = ScriptResults.Success
End Sub

Screenshot #1:

截图# 1:

Screenshot #2:

截图# 2:

Screenshot #3:

截图# 3:

Screenshot #4:

截图# 4:

Screenshot #5:

截图# 5:

Screenshot #6:

截图# 6:

Screenshot #7:

截图# 7:

Screenshot #8:

截图# 8:

Screenshot #9:

截图# 9:

Screenshot #10:

截图# 10:

Screenshot #11:

截图# 11:

Screenshot #12:

截图# 12:

Screenshot #13:

截图# 13:

#3

I just had this issue, after reading some of the replies here, nothing really sorted out my problem and the solutions in here seem insane in terms of complexity.

我刚刚遇到了这个问题，在阅读了一些回复之后，没有什么能真正解决我的问题，而这里的解决方案在复杂性方面似乎很疯狂。

My FTP task was failing since I did not allow overwriting files, lets say the job was kicked off twice in a row, the first pass will be fine, because some files are transferred over but will fail if a local file already exists.

我的FTP任务失败了，因为我不允许覆盖文件，假设作业连续被踢了两次，第一次通过是可以的，因为有些文件被转移了，但是如果本地文件已经存在，那么就会失败。

My solution was simple:

我的解决方案很简单:

1. Right click task - Properties
2. 右键单击任务-属性
3. Set ForceExecutionResult = "Success"
4. 设置ForceExecutionResult =“成功”

#4

(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.)

我使用了一个脚本任务，并为ftp连接信息、源和目标目录提供了一组变量。(因为，我们将更改运行在其上的服务器，在配置包中更改会更容易)。

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

我动态创建一个文本文件，并将ftp命令写到它:

    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:

然后，在给它足够的时间来真正关闭它之后，我启动一个进程来执行ftp命令:

    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!).

然后，在给它一些时间让ftp进程运行之后，我删除了临时的ftp文件(其中包含连接信息!)

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.

如果源目录中不存在文件(该变量具有\\驱动器\dir\*)。*映射)，则没有错误。如果发生其他错误，任务仍然失败，这是应该的。

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.

我是SSIS的新手，这可能是一个蹩脚的组合。但它现在起作用了。我想我要求的是最好的方法，我当然不会说这就是最好的方法。

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.

正如我指出的，我无法知道这些文件的名称，甚至根本不知道那里是否有任何文件。如果他们在那里，我想要他们。

#5

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).

您应该能够在ActiveX脚本任务中设置一个变量，然后使用它来决定FTP任务是否应该运行。这里有一个使用本地路径的示例。希望您能够适应这个概念(或者，如果可能的话，映射FTP驱动器并这样做)。

#6

1) Set the FTP Task property ForceExecutionResult = Success

1)设置FTP任务属性forcetionresult = Success

2) Add this code to FTP Task OnError event handler.

2)将此代码添加到FTP任务OnError事件处理程序中。

    public void Main()
    {
        // TODO: Add your code here

        int errorCode = (int)Dts.Variables["System::ErrorCode"].Value;

        if (errorCode.ToString().Equals("-1073573501"))
        {
            Dts.Variables["System::Propagate"].Value = false;
        }
        else
        {
            Dts.Variables["System::Propagate"].Value = true;
        }


        Dts.TaskResult = (int)ScriptResults.Success;
    }

#7

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

将它放在ForEach容器中，该容器遍历要上载的文件。没有文件，没有FTP，没有失败。

#8

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

失败时可以重定向到另一个不做任何事情的任务(即返回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.

为此，添加新的脚本任务，突出显示FTP任务，第二个绿色连接器将出现，将其拖到脚本任务，然后双击它。选择下拉值上的失败。显然，您需要在这个脚本任务中处理真正的失败，以便仍然在作业历史中显示。

#9

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.

啊哈，好的-谢谢你的澄清。由于FTP任务不能返回列出的文件夹，因此不能像我最初所说的那样使用ForEach—这只有在将X个文件上载到远程源时才可行。

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.

下载X数量的文件,你可以两种方式,要么你可以完全在。net在脚本的任务,或者你可以从内部填充的ArrayList文件名。net脚本任务,然后ForEach ArrayList,传递一个变量和下载的文件名称变量名在标准FTP的任务。

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

代码示例来适应: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/

因此，在上面，您将获得FileNames()并从中填充ArrayList，然后将ArrayList分配给Dts中的一个对象类型变量。为每一个变量,然后在对象(ArrayList)变量使用代码类似:http://www.sqlservercentral.com/articles/SSIS/64014/

#10

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.

您可以使用来自eaSkills的免费SSIS FTP任务+。如果文件或文件不存在，它不会抛出错误，它支持通配符，并为您提供下载和删除(如果需要的话)的选项。

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

这是到特性页面的链接:http://www.easkills.com/ssis/ftptask

#11

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

这是另一个为我工作的解决方案，使用内置的东西，所以无需手动重新编写FTP逻辑:

1) Create a variable in your package called FTP_Error

1)在您的包中创建一个名为FTP_Error的变量。

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

2)单击FTP任务，然后单击“事件处理程序”选项卡

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

3)单击页面内，为“FTP任务/OnError”创建一个事件处理程序——当FTP出现问题时，该处理程序将启动

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

4)从工具箱中，拖动脚本任务项，双击打开它。

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

5)在第一个弹出框中，ReadOnlyVariables——添加System::ErrorCode, System::ErrorDescription

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

6)在第一个弹出框中，ReadWriteVariables——添加您的用户::FTP_Error变量

7) Edit Script

7)编辑脚本

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

8)在脚本中，将FTP_Error变量设置为包含上述ReadOnlyVariables:

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

9) Save and close script

9)保存并关闭脚本。

10) Hit "OK" to script task

10)点击“确定”到脚本任务

11) Go back to "Control Flow" tab

11)回到“控制流”选项卡

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

从FTP任务中，OnError进入一个新的脚本任务，并编辑它。

13) ReadOnlyVariables: User::FTP_Error from before

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)

14)现在，当FTP上没有找到文件时，错误代码是-1073573501(您可以在这里找到错误代码引用列表: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:

15)在你的脚本中，输入你想做的事情的逻辑——如果你发现“没有文件发现”的代码，那么你可能会说任务成功了。否则，任务失败。你的正常流程可以按照你的意愿来处理:

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.

从那里你的成功/失败流将做你想做的事情。

#12

An alternative is to use this FTP File Enumerator

另一种方法是使用这个FTP文件枚举器。