SSIS - 如何遍历文件夹中的文件并获取路径+文件名，最后执行参数为路径+文件名的存储过程 [英] SSIS - How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename

问题描述

非常感谢任何帮助.我正在尝试创建一个 SSIS 包来遍历文件夹中的文件并获取路径 + 文件名，最后执行存储的过程，参数为路径 + 文件名.我不确定如何获取路径+文件名并将其作为参数插入到存储过程中.我附上了截图供您参考:

Any help is much appreciated. I am trying to create an SSIS package to loop through files in the folder and get the Path + filename and finally execute the stored proc with parameter as path+filename. I am not sure how to get the path+filename and insert the into the Stored proc as parameter. I have attached the screenshot for your reference:

推荐答案

看起来你的想法总体上是正确的，@Speedbirt186 提供的链接有一些很好的细节，但听起来我认为有一些细微差别可能会指出流和变量.

Looks like you have the right idea in general and the link @Speedbirt186 provided has some good details but it sounds like there are a couple of nuances that I thought I might point out in regards to flow and variables.

foreach 循环可以指定整个路径或文件名或文件名 &扩展到一个变量.如果您不想添加脚本任务来从路径中拆分文件名，那么后者对您的帮助最大.如果您从向项目中添加 5 个变量开始，它会更容易一些.1 将是源目录路径，另一个是目标(存档)目录路径，然后 1 将保存由 for 每个循环分配的文件名和扩展名.然后是 2 个额外的动态变量，它们简单地组合源目录和文件名以获取源完整路径，以及带有文件名的目标以获取目标完整路径.

The foreach loop can assign the entire path or the file name or file name & extension to a variable. The latter will be the most help in your case if you don't want to add a script task to split the Filename from the path. If you start by adding 5 variables to your project it will make it a little easier. 1 will be the Source Directory Path, another the Destination (Archive) Directory Path, and then 1 to hold the File Name and Extension assigned by the for each loop. Then 2 additional dynamic variables that simply combine the source directory and file name to get the source full path and the destination with file name to get the destination full path.

接下来确保您设置了数据库和 Excel 文件连接.在设置后的 Excel 文件连接中，转到属性窗口中的表达式并将连接字符串"属性设置为 SourceFullPath.这将告诉连接在循环的每次迭代中更改文件路径.

Next make sure you set up your database and Excel file connections. In your Excel file connection after setting it up go to Expressions in the properties window and set the "Connection String" property to SourceFullPath. This will tell the connection to change the file path at every iteration of your loop.

现在您只需要设置循环等.添加每个循环容器的前部，设置目录、过滤器，然后选择文件名和扩展名.

Now you just need to setup your loop etc. Add the fore each loop container setting a directory, filter, and choose File Name and Extension.

现在在集合页面的表达式框中将目录属性设置为源目录变量的属性.

Now in the expression box on the collection page set the directory property to be that of your Source Directory variable.

Fore each 循环的最后一部分是设置变量映射以将文件名存储在变量中.所以去那个选项卡选择你的文件名变量并将索引设置为0.

The last part of the Fore each loop is to set your variable mappings to store the file name in your variable. so go to that tab choose your file name variable and set index to 0.

此时，您可以像使用普通文件一样添加数据流并设置导入(请注意，文件名参数的默认值应该是具有要导入结构的实际文件).

At this point you can add your data flow and setup your import just like you would with a normal file (note your default value for your file name parameter should be to an actual file with the structure you will want to import).

在您的数据流放入执行 SQL 任务并按照您的需要进行设置后.这是一个直接输入的示例，您可以看到引用参数的简单方法只是一个问号 (?).

After your data flow drop in your Execute SQL task and set it up how you need. here is an example of direct input and you can see an easy way to reference a parameter is simply a question mark (?).

接下来在您的 sql 任务中通过添加您需要的详细信息来设置您的参数映射，例如:

Next in your sql task setup your parameter mapping by adding in the details you need such as:

现在您可以执行文件任务了.根据需要删除文件任务和设置，但选择目标和源完整路径变量来告诉任务要移动哪个文件.

Now you are on to your file task. Drop your file task and setup as you desire, but choose your destination and source full path variables to tell the task which file to move.

这样你就完成了.不过还有一件事需要注意.您在发布的图像中设置优先级的方式显示了从数据流到 sql 和同时到文件任务的过程.如果您的存储过程依赖于您的文件，您可能希望将其放在 sql 任务之后.如果您想移动文件，即使您的存储过程失败，您也可以随时将约束选项更改为完成".

that's it your are done. there is 1 more thing to note though. The way you have your precedence set in the image you posted you show going from your data flow to your sql and to your file task simultaneously. If your stored procedure relies on your file you may want to put it after your sql task. You can always change the constraint options to "completion" if you want to move the file even if your stored proc fails.

这篇关于SSIS - 如何遍历文件夹中的文件并获取路径+文件名，最后执行参数为路径+文件名的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！