如何仅使用SSIS包通过目标中不存在的文件进行循环？ [英] How to loop only through files that don't exist in destination using an SSIS package?

问题描述

我在网络上有一个文件夹，我将文件从一个文件夹移动到另一个文件夹。但我只想移动新文件，所以只复制目标文件夹中不存在的文件。怎么样？我已经为每个循环容器和文件系统任务设置了。我正在使用变量。现在，每次执行包时，它会将所有文件从一个文件夹复制到下一个文件夹。是否有某种有条件的工具可以粘在里面？我不是很擅长编写脚本，所以如果这是唯一的解决方案，我可能需要你的帮助。

您可以使用 Foreach循环容器，脚本任务和文件系统任务来实现此目标。下面的例子显示了如何做到这一点。该示例是使用SSIS 2008 R2创建的。

分步过程：

1. 在路径 C：\temp\中创建两个名为 Source 和 Destination 的文件夹如截图＃ 1 所示。 放置一个名为 Sample_File_01.txt的示例文件在文件夹路径 C：\temp\Source\ 中保留另一个文件夹 C：\temp\Destination \ 空。只有当文件已经不存在时，SSIS包才会将Source文件夹中的文件复制到Destination文件夹中。请参阅截图＃ 2 和＃ 3 。
   

2. 在SSIS包中，在屏幕截图中＃ 4 。将变量 DestinationFolder 设置为值 C：\temp\Destination\ 。将变量 SourceFolder 设置为值 C：\temp\Source\ 。将变量 FilePattern 设置为值 *。* 。您可以根据您的要求更改这些变量的值。选择变量 SourceFilePath 并打开属性窗口，方法是按 F4 按钮。将属性 EvaluateAsExpression 更改为 True ，并将属性表达式设置为值 @ [User :: SourceFolder] + @ [User :: FileName] 。请参阅截图＃ 5 。

3. 选择变量 DestinationFilePath 并打开属性窗口，方法是按 F4 按钮。将 EvaluateAsExpression 属性更改为 True ，并将属性表达式设置为值 @ [User :: DestinationFolder] + @ [User :: FileName] 。请参阅截图＃ 6 。
在SSIS包的C 控制流选项卡上，放置一个 Foreach Loop容器，并配置容器的属性，如屏幕截图＃ 7 和＃ 8 所示。确保您在收藏夹部分选择了单选按钮名称和附加信息。



4. 在Foreach循环容器中，放置一个脚本任务。双击脚本任务，然后单击编辑脚本按钮。将脚本任务中的Main（）方法替换为脚本任务代码部分中给出的代码。此代码检查目标文件是否已经存在，然后相应地填充布尔变量 DoesFileExist 。
   

5. 在Foreach循环容器，在脚本任务下放置一个文件系统任务。将脚本任务的成功绿色箭头连接到文件系统任务。配置文件系统任务，如截图＃ 9所示。

不存在于目的地路径中。所以，我们需要更改脚本任务和文件系统任务之间的连接器。右键单击绿色连接器，然后选择编辑，如屏幕截图＃ 10 中所示。


6. 按截图所示配置优先约束＃的 11 即可。这将检查变量 DoesFileExist 是否包含值 False ，这表示在目标中找不到文件。




7. 完成配置后，SSIS包应该如屏幕截图＃ 12 所示。 >屏幕截图＃ 13 显示第一个程序包的执行情况。在执行期间，目标路径 C：\temp\Destination\ 中没有文件。执行后，文件Sample_File_01.txt已经从 C：\temp\Source\ 复制到 C：\temp\ Destination\ 。请参阅截图＃ 14 。

屏幕截图＃第二包执行。在执行过程中，没有文件被复制到目标路径 C：\temp\Destination\ 。正如您可以注意到的那样，文件系统任务没有执行，因为优先级约束失败。

希望有所帮助。

脚本任务代码：
$ b

C＃代码只能在 SSIS 2008及以上 中使用
$ b

  public void Main（）
 {
 Variables varCollection = null; 
 
 Dts.VariableDispenser.LockForRead（User :: DestinationFilePath）; 
 Dts.VariableDispenser.LockForWrite（User :: DoesFileExist）; 
 Dts.VariableDispenser.GetVariables（ref varCollection）; 
 $ b $ varCollection [User :: DoesFileExist]。Value = Convert.ToBoolean（System.IO.File.Exists（varCollection [User :: DestinationFilePath]。Value.ToString（）））; 
 
 Dts.TaskResult =（int）ScriptResults.Success; 
 
  

屏幕截图＃1：

<截图＃2：

截图＃3： https://i.stack.imgur.com/0wUXj.pngalt =3>

屏幕截图4：

屏幕截图5：

屏幕截图＃6： $ b

屏幕截图＃7： / strong>

屏幕截图8：

屏幕截图＃9： $ b

屏幕截图＃10： / strong>

屏幕截图＃11：

截图＃12：

截图＃13：

截图＃14： .stack.imgur.com / HIT3J.pngalt =14>

截图15：

I have a folder on the network with files and I'm moving the files from one folder to another folder. But I only want to move new files, so only copy over files that do not exist in the destination folder. How? I already have the for each loop container and a file system task. I'm using variables. Right now it copies all files from one folder to the next everytime the package is executed. Is there some sort of conditional tool that I can stick in there? I'm not really good at writing scripts so if that is the only solution I may need your help.

解决方案

Here is one possible option that you can achieve this using Foreach Loop Container, Script task and File system task. Following example shows how this can be done. The example was created using SSIS 2008 R2.

Step-by-step process:

1. Create two folders named Source and Destination in the path C:\temp\ as shown in screenshot #1.

2. Place a sample file named Sample_File_01.txt in the folder path C:\temp\Source\ and leave the other folder C:\temp\Destination\ empty. The SSIS package will copy files from the Source folder to Destination folder only if the file already doesn't exist. Refer screenshots #2 and #3.

3. On the SSIS package, create 7 variables as shown in the screenshot #4. Set the variable DestinationFolder to the value C:\temp\Destination\. Set the variable SourceFolder to the value C:\temp\Source\. Set the variable FilePattern to the value *.*. You can change the values of these variables according to your requirements.

4. Select the variable SourceFilePath and open the Properties window by pressing F4 button. Change the property EvaluateAsExpression to True and set the property Expression to the value @[User::SourceFolder] + @[User::FileName]. Refer screenshot #5.

5. Select the variable DestinationFilePath and open the Properties window by pressing F4 button. Change the property EvaluateAsExpression to True and set the property Expression to the value @[User::DestinationFolder] + @[User::FileName]. Refer screenshot #6.

6. On the SSIS package's Control Flow tab, place a Foreach Loop container and configure the properties of the container as shown in screenshots #7 and #8. Make sure that you select the radio button Name and extension on the Collection section.

7. Within the Foreach Loop container, place a Script Task. Double-click on the Script task and click on the Edit Script button. Replace the Main() method inside the script task with the code given under the Script Task Code section. This code checks if the destination file already exists or not and then populates the boolean variable DoesFileExist accordingly.

8. Within the Foreach Loop container, place a File System Task below the Script Task. Connect the Script task's success green arrow to the File System Task. Configure the File System Task as shown in screenshot #9.

9. We need the File System Task to execute only if the file doesn't exist in the destination path. So, we need to change the connector between the Script Task and the File System Task. Right-click on the green connector and select Edit as shown in screenshot #10.

10. Configure the Precedence Constraint as shown in screenshot #11. This checks if the variable DoesFileExist contains the value False, which means the file was not found in the destination.

11. Once configured, the SSIS package should be like as shown in screenshot #12.

12. Screenshot #13 shows the first package execution. During this execution, there were no files in the destination path C:\temp\Destination\. After the execution, the file Sample_File_01.txt has been copied from C:\temp\Source\ to C:\temp\Destination\. Refer screenshot #14.

13. Screenshot #15 shows the second package execution. During this execution, no files were copied to the destination path C:\temp\Destination\. As you can notice, that the File System Task didn't execute because the Precedence constraint failed.

Hope that helps.

Script task code:

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

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::DestinationFilePath");
    Dts.VariableDispenser.LockForWrite("User::DoesFileExist");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    varCollection["User::DoesFileExist"].Value = Convert.ToBoolean(System.IO.File.Exists(varCollection["User::DestinationFilePath"].Value.ToString()));

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

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Screenshot #15:

这篇关于如何仅使用SSIS包通过目标中不存在的文件进行循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！