SSIS帮助:从SQL表中不存在的文件列表中存档文件 [英] SSIS Help: Archive a file from the list of files that does not exist in SQL Table

问题描述

我想在SSIS包中创建此文件，其中，它将在目录或文件夹中的文件列表中循环，并检查每个文件是否存在于SQL查询表中.如果表中不存在文件名，则会将其归档在另一个文件夹中.

I would like to create this in SSIS package, wherein, it will loop in the list of files in a directory or folder and check each files if it exist in a SQL queried table. If the filename does not exist in the table, it will be archive in a different folder.

非常感谢您的帮助！ =)

Thanks is advance for any help! =)

推荐答案

您可以使用For每个Loop容器，该容器可以指向所述目录或文件夹，然后存储文件名(名称和扩展名，完全限定或名称)仅取决于要求)在变量中，例如LoopVariable.在具有准备好的语句的容器内使用Execute SQL任务，例如- 如果存在(从[文件]中选择*，其中[文件名] =?)选择是" 其他选择否"- 在参数中，将LoopVariable和Result映射传递到另一个变量FileExists.在优先约束编辑器中，使用-表达式-[User :: FileExists] ==否"，然后连接到将文件移动到存档文件夹的文件系统任务.
注意:如果您没有从For循环容器中获取完全限定的名称，则可能需要形成文件路径.

You could use a For each Loop container that can be pointed to the said directory or folder and then store the file name (Name and Extension, Fully Qualified or Name only depending on the requirement) in a variable eg. LoopVariable. Use a Execute SQL task inside the container that has a prepared statement like - If exists (select * from [Files] where [FileName] = ?)Select 'Yes' Else Select 'No' - In the Parameter pass the LoopVariable and Result map to another variable FileExists. In the precedence Constraint editor use - expression - [User::FileExists] =="No" and connect to a file system task that moves the file to the archive folder.
Note: You might need to form the file path in case you are not getting the fully qualified name from the For loop container.

这篇关于SSIS帮助:从SQL表中不存在的文件列表中存档文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！