为对象变量创建表达式? [英] Creating an Expression for an Object Variable?
问题描述
我正在创建一个 SSIS 包，第一步是获取存储在文件中的文件列表并将此信息存储在对象变量中.我将此变量传递给尝试打开文件列表的任务.但是，如果因为文件夹为空而没有要打开的文件，则打开文件任务将失败.
I am creating an SSIS package and the first step is to get a list of files stored in a file and store this information in an object variable. I pass this variable to a task that tries to open the list of files. However, the open file task fails if there are no files to open because the folder is empty.
我对使用 SSIS 比较陌生，很好奇如何重写一个表达式来检查对象变量是否为空，如果为空则不允许打开文件任务运行.
I am relatively new to using SSIS and was curious on how to rewrite an expression that checks to see if the object variable is empty and if it is empty not allow the open file task to run.
推荐答案
您不需要使用对象变量来遍历文件夹中的文件.您可以使用 Foreach Loop Container 轻松实现这一点.以下示例演示了如何做到这一点.该示例是在 SSIS 2008 R2 中创建的.
You don't need to use an object variable to loop through files in a folder. You can easily achieve that using Foreach Loop Container. Following example demonstrate how this can be done. The example was created in SSIS 2008 R2.
分步过程:
假设我们需要循环路径 C:\temp 中的文件，如屏幕截图 #1 所示.
在 SSIS 包上，创建三个变量，即 FolderPath、FilePattern 和 FilePath.请参阅屏幕截图 #2.将 FolderPath 变量设置为您想要循环的文件夹，在本例中，我选择了 C:\temp.将 FilePattern 变量设置为您的文件应该匹配的模式，在这里我想遍历所有文件，所以我使用了 *.*.如果您只想遍历 Excel 2010 文件，则可以使用 *.xlsx.它只能接受一种模式.不要为变量 FilePath 设置任何值，因为当 Foreach Loop Container 循环遍历文件夹中的每个文件时，会为其分配一个值.
On the SSIS package, create three variables namely FolderPath, FilePattern and FilePath. Refer screenshot #2. Set the FolderPath variable to the folder that you would like to loop through, here in this case I have chosen C:\temp. Set the FilePattern variable to a pattern that your files should match, here I would like to loop through all files so I have used *.*. If you would like to loop through only Excel 2010 files, then you can use *.xlsx. It can accept only one pattern. Don't set any value to variable FilePath because this will be assigned with a value when the Foreach Loop Container loops through each file in the folder.
在包的控制流选项卡上，放置一个Foreach循环容器，然后在Foreach循环容器中放置一个脚本任务.在这个例子中，我们将简单地循环遍历每个文件并显示它们的名称，不再做任何事情.请参阅屏幕截图 #3.
On the package's Control Flow Tab, place a Foreach Loop container and then place a Script task within the Foreach loop container. In this example, we are going to simply loop through each file and display their names and not do anything more. Refer screenshot #3.
配置 Foreach 循环容器，如屏幕截图 #4 和 #5 所示.在Collection 部分，我们已将表达式配置为使用变量FolderPath 和FilePattern.在变量映射部分，我们告诉容器将文件路径值存储到FilePath变量中.
Configure the Foreach Loop Container as shown in screenshots #4 and #5. On the Collection section, we have configured the Expressions to use the variables FolderPath and FilePattern. On the Variable Mappings section, we have told the container to store the file path value into the FilePath variable.
在脚本任务中，将 Main() 方法代码替换为脚本任务代码部分下给出的代码.代码中没有什么花哨的.它只是在消息框中显示文件路径.
Inside the Script task, replace the Main() method code with the code given under Script Task Code section. There is nothing fancy in the code. It simply displays the file path in a message box.
屏幕截图 #6 - #11 显示了一个示例包执行以及文件夹中的每个文件是如何循环的.请注意截图#11，脚本任务被标记为绿色，表示包成功执行了任务.
Screenshots #6 - #11 shows a sample package execution and how each file in the folder is looped through. Please note the screenshot #11, the script task is marked with green color stating that the package successfully executed the task.
现在，让我们删除文件夹 C:\temp 中的所有文件，如屏幕截图 #12 所示.
Now, let's delete all the files in the folder C:\temp as shown in screenshot #12.
如果我们现在执行包，Foreach 循环容器内的任务将不会执行，因为没有文件可以循环，文件夹是空的.请参阅屏幕截图 #13.请注意，脚本任务被标记为白色，表明包没有执行脚本任务并跳过了该部分.
If we execute the package now, the tasks within the Foreach loop container will not executed because there are no files to loop through and the folder is empty. Refer screenshot #13. Please note that the script task is marked with white color stating that the package didn't execute the script task and it skipped the section.
这只是一个简单的例子.您可以做的不仅仅是显示文件的名称.您可以在 Foreach 循环容器中执行其他任务并传递 FilePath 变量来处理文件.
This is only a simple example. You can do whole lot more than simply display names of the files. You can have other tasks within the Foreach Loop container and pass the FilePath variable to process the files.
希望有所帮助.
脚本任务代码:
C# 只能在 SSIS 2008 及更高版本 中使用的代码.
C# code that can be used only in SSIS 2008 and above.
public void Main()
{
Variables varCollection = null;
Dts.VariableDispenser.LockForWrite("User::FilePath");
Dts.VariableDispenser.GetVariables(ref varCollection);
MessageBox.Show(varCollection["User::FilePath"].Value.ToString(), "File Path");
Dts.TaskResult = (int)ScriptResults.Success;
}
屏幕截图 #1:
屏幕截图 #2:
屏幕截图 #3:
屏幕截图 #4:
屏幕截图 #5:
屏幕截图 #6:
屏幕截图 #7:
截图 #8:
屏幕截图 #9:
屏幕截图 #10:
屏幕截图 #11:
屏幕截图 #12:
屏幕截图 #13:
这篇关于为对象变量创建表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
