我如何能使用foreach循环容器在SSIS遍历多维数组？ [英] 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，目录路径从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 变量。目前，在容器收集选项卡，我有枚举器设置的Foreach自变量枚举器，与变量设置为用户:: DirectoryList 。我有变量用户:: DirectoryID 设置为指数0和用户::目录路径设置为索引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自变量枚举器是不是一个我应该选择，或者在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

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