继续处理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.
分步过程:
-
在SSIS包上,创建一个名为 FTP 的
FTP Connection
,还创建 5 变量,如屏幕快照# 1 中所示. .变量RemotePath
包含FTP文件夹路径;LocalPath
包含将文件放到其中的文件夹;FilePattern
包含用于从FTP服务器下载文件列表的文件模式;FileName
将由Foreach loop container
填充,但为避免FTP任务设计时间错误,可以使用/填充它,或者可以将FTP Task的DelayValidation
属性设置为是.
On the SSIS package, create an
FTP Connection
named FTP and also create 5 variables as shown in screenshot #1. VariableRemotePath
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 theForeach loop container
but to avoid FTP task design time error, it can be populated with / or theDelayValidation
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屋!