如何在 SSIS 中使用 foreach 循环容器遍历多维数组? [英] How can I use a foreach loop container in SSIS to iterate through a multidimensional array?

问题描述

我有一个 SSIS 包，它使用脚本和一个 foreach 容器以及其他一些东西来更新几个表.我正在使用 SqlConnection 和 SqlCommand 在 foreach 容器之前的脚本任务中调用此查询:

I have an SSIS package that's updating a couple tables using scripts and a foreach container among a couple other things. I'm using SqlConnection and SqlCommand to call this query in a script task before the foreach container:

SELECT TOP 10 DirectoryID, DirectoryPath FROM ProjectDirectory ORDER BY LastAudit

并且正在使用 SqlDataReader 将它们添加到多维数组 [10,2].我将数组分配给对象变量 DirectoryList，以便我可以在 foreach 循环容器中遍历它.它应该看起来像 { {"1", "C:Folder1"}, {"2", "C:Folder2"}, ...} 我相信.

and am using SqlDataReader to add them to a multidimensional array[10,2]. I assign the array to an object variable DirectoryList so that I can iterate through it in a foreach loop container. It should look like { {"1", "C:Folder1"}, {"2", "C:Folder2"}, ...} I believe.

问题是我几天前才开始使用这个软件，对 foreach 循环容器如何循环遍历 DirectoryList 变量感到困惑.目前，在容器的集合选项卡中，我将 Enumerator 设置为 Foreach From Variable Enumerator，变量设置为 User::DirectoryList.我将变量 User::DirectoryID 设置为索引 0，并将 User::DirectoryPath 设置为索引 1.我试图让它循环通过顶层数组，以便在每个循环中将 ID 和路径变量分配给1"和C:Folder1"，然后在下次通过循环时将它们分配给2"和C:Folder2""等.我有一个脚本组件，它使用路径来查找一些信息，我使用 id 来更新 sql 表的正确行.

The issue is that I just began using this software a couple days ago, and am confused at how the foreach loop container loops through the DirectoryList variable. At the moment, in the collection tab of the container, I have the Enumerator set to Foreach From Variable Enumerator, with the variable set as User::DirectoryList. I have variables User::DirectoryID set to index 0, and User::DirectoryPath set to index 1. I'm trying to get it to loop through just the top level of the array, so that in each loop the ID and path variables are assigned to "1" and "C:Folder1", and then next time it passes through the loop they are assigned to "2" and "C:Folder2", etc. I have a script component that uses the path to find some info, and I use the id to update the correct row of an sql table.

为了调试，我让它向我显示第一次迭代的 ID 和路径 MessageBox.Show(Variables.DirectoryID + ", " + Variables.DirectoryPath)，它显示8, 8".8 对应于应该首先显示的行的 id.下次它运行时，它给了我两次正确的路径，C:Folder1, C:Folder1".所以显然变量映射不起作用，它将两个变量映射到同一个索引.在任何情况下它都不起作用，所以我想要么(也许两者都有?)Foreach From Variable Enumerator 不是我应该选择的，或者我不能在 foreach 容器中使用多维数组.我已经搜索并没有发现任何东西，这一切可能很难理解，但有什么建议吗?

To debug, I'm having it show me the ID and path MessageBox.Show(Variables.DirectoryID + ", " + Variables.DirectoryPath) of the first iteration, and it's showing "8, 8". The 8 corresponds to the id of the row that should show up first. The next time it runs through, it gives me the correct path twice, "C:Folder1, C:Folder1". So apparently the variable mapping is not working, it's mapping both variables to the same index. In any case It's not working, so I'm thinking that either (and maybe both?) the Foreach From Variable Enumerator is not the one I should choose, or that I can't use a multidimensional array in a foreach container. I've searched and found nothing and this all may be hard to understand, but any suggestions?

推荐答案

好的，您实际上不必那样做.由于您的源是一个 t-sql 查询，您可以使用执行 sql 任务"来运行它.但这很好，它也可以通过这种方式完成.我现在实际上正在开发一个包，将结果集放入脚本任务的数据表中，并将其加载到对象变量中，就像您对 DirectoryList 所做的一样.

ok, you actually dont have to do that way. Since your source is a t-sql query you could have ran it with an "execute sql task". But that's fine, it can be done this way too. I'm actually working on a package right now that get a result set into a datatable on a script task and loads it into a object variable, like you do with your DirectoryList.

要读取值，只需像这样配置循环，其中变量是对象变量:

To read the values, just configure your loop like this, where the variable is the Object variable:

并在映射选项卡上，如下所示:其中变量是您希望放置第一列(索引 0)的值的包变量.您可以拥有与结果集中的列一样多的索引.

and on the mappings tab, like this: where the variable is the package variable where you want the value of the first column (index 0) to be placed in. You can have as many indexes as you have columns on your result set.

就是这样，在循环中，您将可以使用当前值访问变量

and that's it, inside your loop you will have access to your variables with the current values

这篇关于如何在 SSIS 中使用 foreach 循环容器遍历多维数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！