如何创建一个包，将给定文件夹中的所有文件复制到新文件夹中? [英] How do I create a package that would copy all files from a given folder into a new folder?

问题描述

我有一个名为Data"的文件夹，其中包含文件名中具有当前日期的所有文件，例如myfile 20-08-2011".现在我想创建一个 SSIS 包，它收集 08 月的所有文件，即，我想按月整理文件并将这些文件复制到一个名为八月"的新文件夹中.我该怎么做?

I have one folder called 'Data' that contains all files with current date in filename like for example 'myfile 20-08-2011'. Now I want to create an SSIS package which collects all files which are from the month 08, that is, I want to sort out files by month wise and copy those files into a new folder called "august". How can I do that?

推荐答案

这是在 Foreach 循环容器、Script Task 和 Script Task 的帮助下实现这一目标的一种可能解决方案文件系统任务.您可以在没有文件系统任务的情况下执行此操作.但是，我已经使用它来利用内置的控制流任务来移动文件.该示例是使用 SSIS 2005 创建的.

Here is one possible solution to achieve this with the help of Foreach loop container, Script Task and File System Task. You can do this without File System Task. However, I have used it to make use of the in-built control flow task to move the files. The example was created using SSIS 2005.

该示例假设文件将被统一命名.因此，该示例使用格式文件 DD-MM-YYYY.例如，文件将命名为File 29-07-2011、File 15-08-2011 等

The example assumes that the files will be named uniformly. So, the example uses the format File DD-MM-YYYY. For example, the files will be named File 29-07-2011, File 15-08-2011 etc.

在 SSIS 包上，创建以下变量.在此示例中，源文件存储在文件夹位置 F:\Temp\ 中，文件应移动到位置 *F:\Temp\Monthwise*.在目标文件夹中，将有每个月的文件夹，例如 7 月、8 月等.

On the SSIS package, create the following variables. In this example, the source files are stored in the folder location F:\Temp\ and the files should be moved to the location *F:\Temp\Monthwise*. Within the destination folder, there will be folders for each month like July, August etc.

• DestinationFolder 变量将保存最终目标文件夹的值，如 F:\Temp\Monthwise\August 但这个变量将被分配与内部的实际值脚本任务.现在，让我们分配值 F:\Temp\Monthwise\.这个临时值是为了避免文件系统任务在设计时抛出错误消息.

• DestinationFolder variable will hold the final destination folder value like F:\Temp\Monthwise\August but this variable will be assigned with the actual value inside the Script task. For now, let's assign the value F:\Temp\Monthwise\. This temporary value is to avoid File System Task from throwing error messages at design time.

DestinationRoot 将包含实际的根文件夹，在该文件夹下应根据月份名称创建 7 月、8 月等文件夹.

DestinationRoot will contain the actual root folder under which the folders like July, August should be created based on the month names.

SourceFolder 表示最初存储所有文件的文件夹.在此示例中，源文件夹将是 F:\Temp\

SourceFolder denotes the folder in which all the files are initially stored. Here in this example, the source folder will be F:\Temp\

SourceFilePath 表示实际文件路径.当 Foreach 循环容器循环遍历每个变量时，将为该变量分配单个文件值.为了避免文件系统任务在设计时抛出错误消息，让我们为它分配一些虚拟值 F:\Temp\1.txt.

SourceFilePath denotes the actual file path. This variable will be assigned with the individual file values when the Foreach loop container loops through each variable. To avoid the File System Task from throwing error messages at design time, let's assign it with some dummy value F:\Temp\1.txt.

FilePattern 定义应该在给定源文件夹路径中循环的文件模式.让我们分配*.*，这意味着所有文件都将被循环.您也可以指定 *.txt 或 File*.txt 或 My*.xls 等.这取决于您的要求.

FilePattern defines the file pattern that should be looped through in the given source folder path. Let's assign *.*, which means all the files will be looped through. You can also specify *.txt or File*.txt or My*.xls etc. It is upto your requirements.

MonthStartPosition 表示月份值在文件名中的开始位置.因此，在文件名格式 File 29-07-2011 中，07 月从第 9 个字符开始.因此值为 9.

MonthStartPosition denotes the position where the month value starts in the file name. So, in the file name format File 29-07-2011, the month 07 starts at 9th character. Hence the value 9.

MonthLength 指定要提取的字符数.无论如何，这将是 2 个字符，但我不想硬编码.所以，我创建了一个变量.

MonthLength specifies the number of character to extract. This will anyways be 2 characters but I didn't want to hard code. So, I created a variable.

MonthNameFormat 指定文件夹的创建方式.值 MMMM 表示它将创建具有完整月份名称的文件夹，如一月、二月等.如果我们使用值 MMM，则文件夹将创建为 Jan、Feb 等.仅当文件夹不存在时才会创建这些文件夹.

MonthNameFormat specifies how the folders should be created. Value MMMM denotes that it will create the folders with full month names like January, February etc. If we use the value MMM, the folders will be created as Jan, Feb etc. The folders will be created only if they didn't exist.

在 SSIS 包的 控制流 选项卡上，放置一个 Foreach 循环容器 并将其配置为循环通过变量 SourceFolder 中指定的文件夹使用文件模式变量 FilePattern.当 Foreach 循环容器遍历文件时，文件名将分配给变量 SourceFilePath.我们将使用这个变量来获取脚本任务中的月份值.

On the SSIS package's Control Flow tab, place a Foreach loop container and configure it to loop through the folder specified in the variable SourceFolder using the file pattern variable FilePattern. As the Foreach loop container loops through the files, the file names will be assigned to the variable SourceFilePath. We will use this variable to fetch the month value in Script Task.

在 Foreach 循环容器中，放置一个 Script Task 并在脚本任务的脚本部分单击设计脚本...按钮以打开 VSTA 编辑器并粘贴这些屏幕截图后提供的代码.由于示例是在 VS 2005 中创建的，因此代码是用 VB.NET 编写的，因为这是 SSIS 2005 中唯一支持的语言.

Within the Foreach loop container, place a Script Task and on the script task's Script section click the Design script... button to open the VSTA editor and paste the code provided after these screenshots. Since the example was created in VS 2005, the code is written in VB.NET because that is the only supported language in SSIS 2005.

脚本任务代码:代码从变量 SourceFilePath 中获取完整的文件路径值，并仅提取文件名以将其存储在局部变量 FileName 中.

Script Task Code: The code gets the full file path value from the variable SourceFilePath and extracts only the file name to store it in the local variable FileName.

然后检查是否为 MonthStartPosition 和 MonthLength 变量分配了正确的非零值.然后提取月份值并将其存储在局部变量 MonthValue 中.

Then checks to see if the MonthStartPosition and MonthLength variables are assigned with proper non-zero values. It then extracts the month value to store it in the local variable MonthValue.

使用 MonthValue，它使用 DateTime 函数获取完整的月份名称值.值 1 被分配给日和年，因为我们只需要月份名称.

Uisng the MonthValue, it fetches the full month name value using the DateTime function. The values 1 are assigned to day and year because we only want the Month name.

局部变量 FolderName 中的月份名称与 DestinationRoot 值结合以检查文件夹是否存在.如果该文件夹不存在，将创建该文件夹，以便文件系统任务不会失败.

The month name in the local variable FolderName is combined with the DestinationRoot value to check if the folder exists or not. If the folder doesn't exist, the folder will be created so that the File System Task doesn't fail.

最后将完整的目标文件夹值分配给包变量 DestinationFolder.该变量将用于文件系统任务.

Finally the full destination folder value is assigned to the package variable DestinationFolder. This variable will be used in the File System Task.

SSIS 2005 的 VB.NET 代码

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        Dim varCollection As Variables = Nothing
        Dts.VariableDispenser.LockForRead("User::SourceFilePath")
        Dts.VariableDispenser.LockForRead("User::DestinationRoot")
        Dts.VariableDispenser.LockForRead("User::MonthStartPosition")
        Dts.VariableDispenser.LockForRead("User::MonthLength")
        Dts.VariableDispenser.LockForRead("User::MonthNameFormat")
        Dts.VariableDispenser.LockForWrite("User::DestinationFolder")
        Dts.VariableDispenser.GetVariables(varCollection)

        Dim SourceFilePath As String = varCollection("User::SourceFilePath").Value.ToString()
        Dim FileName As String = SourceFilePath.Substring(SourceFilePath.LastIndexOf("\") + 1)
        Dim DestinationRoot As String = varCollection("User::DestinationRoot").Value.ToString()
        Dim MonthStartPosition As Integer = Convert.ToInt32(varCollection("User::MonthStartPosition").Value)
        Dim MonthLength As Integer = Convert.ToInt32(varCollection("User::MonthLength").Value)
        Dim MonthValue As Integer = 0
        Dim MonthNameFormat As String = varCollection("User::MonthNameFormat").Value.ToString()
        Dim FolderName As String = String.Empty
        Dim MonthwiseDirectory As String = String.Empty

        If MonthStartPosition > 0 AndAlso MonthLength > 0 Then
            MonthValue = Convert.ToInt32(FileName.Substring(MonthStartPosition - 1, MonthLength))
        End If

        If FileName.Length > 0 AndAlso MonthValue > 0 Then
            FolderName = New DateTime(1, MonthValue, 1).ToString(MonthNameFormat)
        End If

        MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName)

        If Not System.IO.Directory.Exists(MonthwiseDirectory) Then
            System.IO.Directory.CreateDirectory(MonthwiseDirectory)
        End If

        varCollection("User::DestinationFolder").Value = MonthwiseDirectory

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

SSIS 2008 及更高版本的 C# 代码

public void Main()
{
    Variables varCollection = null;
    Dts.VariableDispenser.LockForRead("User::SourceFilePath");
    Dts.VariableDispenser.LockForRead("User::DestinationRoot");
    Dts.VariableDispenser.LockForRead("User::MonthStartPosition");
    Dts.VariableDispenser.LockForRead("User::MonthLength");
    Dts.VariableDispenser.LockForRead("User::MonthNameFormat");
    Dts.VariableDispenser.LockForWrite("User::DestinationFolder");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    string SourceFilePath = varCollection["User::SourceFilePath"].Value.ToString();
    string FileName = SourceFilePath.Substring(SourceFilePath.LastIndexOf('\\') + 1);
    string DestinationRoot = varCollection["User::DestinationRoot"].Value.ToString();
    int MonthStartPosition = Convert.ToInt32(varCollection["User::MonthStartPosition"].Value);
    int MonthLength = Convert.ToInt32(varCollection["User::MonthLength"].Value);
    int MonthValue = 0;
    string MonthNameFormat = varCollection["User::MonthNameFormat"].Value.ToString();
    string FolderName = string.Empty;
    string MonthwiseDirectory = string.Empty;

    if (MonthStartPosition > 0 && MonthLength > 0)
    {
        MonthValue = Convert.ToInt32(FileName.Substring(MonthStartPosition - 1, MonthLength));
    }

    if (FileName.Length > 0 && MonthValue > 0)
    {
        FolderName = new DateTime(1, MonthValue, 1).ToString(MonthNameFormat);
    }

    MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName);

    if (!System.IO.Directory.Exists(MonthwiseDirectory))
    {
        System.IO.Directory.CreateDirectory(MonthwiseDirectory);
    }

    varCollection["User::DestinationFolder"].Value = MonthwiseDirectory;

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

在 Foreach 循环容器中，将文件系统任务放在脚本任务之后.如屏幕截图所示配置文件系统任务.

Within the Foreach loop container, place File System Task after the Script task. Configure the File System Task as shown in the screenshot.

配置包任务后，控制流"选项卡应如下所示.

Once the package tasks are configured, the Control Flow tab should look like as shown below.

让我们测试一下这个包.在此之前，源文件夹 F:\Temp 的内容如下所示.这些文件是虚拟的.因此，大小为 0 KB.

Let's test the package. Before that, the contents of the source folder F:\Temp are shown below. The files are dummy. Hence, the size 0 KB.

下面的截图显示了包的成功执行.

Below screenshot shows the successful execution of the package.

下面的屏幕截图显示了如何将文件移动到基​​于月份名称创建的相应目标文件夹中.各个文件夹的内容如下所示.

Below screenshots show how the files have been moved to respective destination folder that have been created based on the month names. Contents of the individual folders are shown below.

希望有所帮助.

这篇关于如何创建一个包，将给定文件夹中的所有文件复制到新文件夹中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！