如何使用 SSIS 包中的 Foreach 循环容器选择最近创建的文件夹? [英] How do I pick the most recently created folder using Foreach loop container in SSIS package?

问题描述

我在 SSIS 方面遇到了一个有趣的挑战.使用 for-each 文件枚举器，我需要选择最近创建的子文件夹，然后遍历每个文件.

I've got an interesting challenge with SSIS. Using a for-each file enumerator, I need to pick the subfolder which has been most recently created, and then iterate through each of the files.

也许一个例子会更好地解释.文件夹看起来像这样:

Perhaps an example would explain better. The folders look something like this:

c:data2011-0703

c:data2011-0703

c:data2011-0626

c:data2011-0626

c:data2011-0619

c:data2011-0619

您如何获得每个文件枚举器以选择最近的文件夹?这可以通过查看创建日期或比较文件名来实现.

How could you get a for each file enumerator to pick the most recent folder? This could either be by looking at the creation date, or comparing the file names.

我猜它会用枚举器中的表达式来完成，只是不知道怎么做！网上也没找到.

I'm guessing it would be done with an expression in the enumerator, just can't work out how! Couldn't find anything on the net either.

谢谢

推荐答案

这里有一个可能的选项，您可以在 Script Task 的帮助下实现这一点.以下示例显示了如何做到这一点.该示例是在 SSIS 2008 R2 中创建的.

Here is one possible option that you can achieve this with the help of Script Task. Following example shows how this can be done. The example was created in SSIS 2008 R2.

分步过程:

1. 在文件夹路径C中创建三个名为2011-0619、2011-0626和2011-0703的文件夹: emp 如屏幕截图 #1 所示.记下每个文件夹的 Date created 值.

1. Create three folders named 2011-0619, 2011-0626 and 2011-0703 in the folder path C: emp as shown in screenshot #1. Make note of the Date created value of each of the folders.

在每个文件夹中放置几个​​文件，如屏幕截图 #2 - #4 所示.

Place few files in each of the folders as shown in screenshots #2 - #4.

在 SSIS 包上，创建四个变量，如屏幕截图 #5 所示.将变量 RootFolder 设置为值 C: emp(在您的情况下，这将是 c:data).使用值 *.* 设置变量 FilePattern.变量 RecentFolder 将被分配到脚本任务中最近的文件夹路径.为避免设计时错误，请为变量 RecentFolder 分配有效的文件路径.当文件在最近的文件夹中循环时，变量 FilePath 将被赋值.

On the SSIS package, create four variables as shown in screenshot #5. Set the variable RootFolder with value C: emp (in your case this will be c:data). Set the variable FilePattern with value *.*. Variable RecentFolder will be assigned with the recent folder path in the Script Task. To avoid design time errors, assign the variable RecentFolder with a valid file path. Variable FilePath will be assigned with values when the files are looped through in the recent folder.

在 SSIS 包上，放置一个脚本任务.将脚本任务中的 Main() 方法替换为 脚本任务代码(获取最近的文件夹): 部分下给出的脚本任务代码.此脚本获取根文件夹中的文件夹列表并循环检查创建日期时间以选择最近创建的文件夹.然后将最近创建的文件夹路径存储在变量 RecentFolder 中.

On the SSIS package, place a Script Task. Replace the Main() method within the Script Task with the script task code give under section Script task code (Get recent folder):. This script gets the list of folders in the root folder and loops through to check the creation datetime to pick the most recently created folder. The recently created folder path is then stored in the variable RecentFolder.

在 SSIS 包上，放置一个 Foreach Loop 容器并按照屏幕截图 #6 和 #7 中所示进行配置.

On the SSIS package, place a Foreach Loop container and configure it as shown in screenshots #6 and #7.

在 Foreach 循环容器中放置一个脚本任务.将脚本任务中的 Main() 方法替换为 脚本任务代码(显示文件名): 部分下给出的脚本任务代码.此脚本仅显示最近创建的文件夹中的文件名.

Place a Script Task inside the Foreach Loop container. Replace the Main() method within the Script Task with the script task code give under section Script task code (Display file names):. This script simply displays the names of files within the recently created folder.

配置完所有任务后，程序包应如屏幕截图 #8 所示.

Once all tasks are configured, the package should look like as shown in screenshot #8.

屏幕截图 #9 - #11 显示包显示了最近创建的文件夹 2011-0703 中的文件名.

Screenshots #9 - #11 show that the package displays the file names in the recently created folder 2011-0703.

希望有所帮助.

脚本任务代码(获取最近的文件夹):

C# 只能在 SSIS 2008 及更高版本中使用的代码.

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

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::RootFolder");
    Dts.VariableDispenser.LockForWrite("User::RecentFolder");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    string rootFolder = varCollection["User::RootFolder"].Value.ToString();
    DateTime previousFolderTime = DateTime.MinValue;
    string recentFolder = string.Empty;

    foreach (string subFolder in System.IO.Directory.GetDirectories(rootFolder))
    {
        DateTime currentFolderTime = System.IO.Directory.GetCreationTime(subFolder);
        if (previousFolderTime == DateTime.MinValue || previousFolderTime <= currentFolderTime)
        {
            previousFolderTime = currentFolderTime;
            recentFolder = subFolder;
        }
    }

    varCollection["User::RecentFolder"].Value = recentFolder;

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

脚本任务代码(显示文件名):

C# 只能在 SSIS 2008 及更高版本中使用的代码.

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

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::FilePath");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(varCollection["User::FilePath"].Value.ToString(), "File Path");

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

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

这篇关于如何使用 SSIS 包中的 Foreach 循环容器选择最近创建的文件夹?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！