继续处理SSIS中的某些特定错误 [英] Continue flow on certain specific Error in SSIS

问题描述

在SSIS 2005中，我正在使用FTP任务.我有一个流程，当程序包运行时，它会从FTP到本地文件夹中检索特定文件夹中的所有文件.

In SSIS 2005, I am using the FTP Task. I have a flow where when the package runs, it retrieves any files in a specific folder from FTP to a local folder.

远程文件夹路径由/root/abc/*abc.txt之类的变量设置

Remote folder path is set by variable such as /root/abc/*abc.txt

如果该文件夹中有符合此条件的文件，则该任务运行正常.如果没有文件，则任务失败，并显示找不到文件"错误！

The task works fine if there are files in that folder matching this criteria. If there are no files, the task fails with a file not found error!

如果远程文件夹中没有匹配的文件，我怎么才能使SSIS不会中断任务，以防出现找不到此特定文件的错误?

How can I make SSIS not break the task in case this specific file not found error comes up simply becuase the remote folder had no matching files?

但是，如果出现诸如FTP服务器无法登录等错误，则该任务应引发预期的错误.

But, in case there is an error such as FTP server not able to login etc., then the task should throw the expected error.

推荐答案

可能您已经找到问题的答案.这是实现此目的的一种可能方法. Script Task可用于查找给定模式的FTP文件夹路径中存在的文件列表(例如*.txt).下面的示例显示了如何完成此操作.

Probably, you have found an answer to your question by now. 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.

分步过程:

1. 在SSIS包上，创建一个名为 FTP 的FTP Connection，还创建 5 变量，如屏幕快照# 1 中所示. .变量RemotePath包含FTP文件夹路径； LocalPath包含将文件放到其中的文件夹； FilePattern包含用于从FTP服务器下载文件列表的文件模式； FileName将由Foreach loop container填充，但为避免FTP任务设计时间错误，可以使用/填充它，或者可以将FTP Task的DelayValidation属性设置为是.

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软件包上，将Script Task，Foreach Loop container和FTP Task放在Foreach Loop container内，如屏幕快照# 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.

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

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.

配置Foreach Loop container，如屏幕快照# 3 和# 4 中所示.该任务将遍历变量** ListOfFiles *.如果没有文件，则循环容器内的FTP任务将不会执行.如果有文件，则将根据在FTP服务器上找到的文件数执行该任务，以执行循环容器内的FTP任务.

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.

配置FTP Task，如屏幕快照# 5 和# 6 中所示.

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

屏幕快照# 7 显示在没有模式*.txt的匹配文件时找到示例包的执行情况.

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

屏幕快照# 8 显示了执行软件包之前C:\temp\ 文件夹的内容.

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

屏幕快照# 9 显示了找到与模式*.xls匹配的文件时示例包执行.

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

屏幕快照# 10 显示FTP远程路径/Practice/Directory_New的内容.

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

屏幕快照# 11 显示了执行包后C:\temp\ 文件夹的内容.

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

屏幕快照# 12 显示了错误的远程路径时软件包失败的情况.

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

屏幕快照# 13 显示与软件包故障相关的错误消息.

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

希望有帮助.

脚本任务代码:

C#代码.

使用System.Text.RegularExpressions 包含using语句；

Include the using statement using System.Text.RegularExpressions;

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;
}

可以在 SSIS 2005 and above 中使用的

VB 代码.

包含Imports语句导入System.Text.RegularExpressions

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

屏幕截图1:

屏幕截图2:

屏幕截图3:

屏幕截图4:

屏幕截图5:

屏幕截图6:

屏幕截图7:

屏幕截图8:

屏幕截图9:

屏幕截图10:

屏幕截图11:

截屏#12:

屏幕截图13:

这篇关于继续处理SSIS中的某些特定错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！