如何使用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:\ data \ 2011-0703

c:\data\2011-0703

c:\ data \ 2011-0626

c:\data\2011-0626

c:\ data \ 2011-0619

c:\data\2011-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:\temp\中创建三个名为2011-0619，2011-0626和2011-0703的文件夹，如屏幕快照# 1 所示.记下每个文件夹的Date created值.

1. Create three folders named 2011-0619, 2011-0626 and 2011-0703 in the folder path C:\temp\ 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:\temp\(在您的情况下为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:\temp\ (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屋！