SSIS 脚本任务:从数组填充对象变量 [英] SSIS Script Task: Populate object variable from Array
问题描述
在 SSIS 中，我使用脚本任务为每个 *.xml 文件运行 For Each 循环在我的文件夹中.所有 .xml 文件都将其名称传递到两个数组之一，arrayA 和 arrayB.
In SSIS, I am using a Script Task to run a For Each loop for each *.xml file in my folder. All .xml files will have their name passed into one of two arrays, arrayA and arrayB.
在脚本的末尾，我尝试为每个数组运行一个 for 循环，将每个存储的值添加到相关的对象变量 objectA 和 objectB 中.
At the end of the script, I am trying to run a for loop for each array, adding each stored value into a related object variable, objectA and objectB.
从数组填充对象变量的正确语法是什么?当我尝试在脚本任务之外在下面的 for each 循环(处理每个文件)中使用它们时，我得到一个错误:分配给变量的值类型与当前变量类型不同
What is the correct syntax to populate an object variables from an array? When I try to use them outside the script task in a for each loop below (to process each file), I get a error: The type of the value being assigned to variable differs from the current variable type
// For the sake of the question, it doesn't matter what A and B mean. I'm just trying to show how the logic structured in a simplified way.
public void Main()
{
// Reset Variable
Dts.Variables["FileARecordCount"].Value = 0;
string NotProcessedDirectory = Dts.Variables["NotProcessedPath"].Value.ToString();
string FileDirectory = Dts.Variables["FullPath"].Value.ToString();
string[] files = Directory.GetFiles(FileDirectory, "*.xml");
// Setting up our arrays which will be used to populate our object variables.
// Each is set to a size of 30, but this can be changed if needed.
string[] FileAFileCollection = new string[30];
string[] ShipmentInformationCollection = new string[30];
int FileAIndex = 0;
int InfoIndex = 0;
// We're going to examine each xml file in our directory
foreach (string file in files)
{
FileInfo CurrentFile = new FileInfo(file);
// First, let's identify FileA files
if (CurrentFile.LastWriteTime < DateTime.Now.AddMinutes(-10))
{
// Add each filename into an array which will populate our package object variable
FileAFileCollection[FileAIndex] = CurrentFile.Name.ToString();
FileAIndex++;
// Before we move the file, let's check to see if the file exists already in the NotProcessed directory.
if (File.Exists(NotProcessedDirectory + CurrentFile.Name))
File.Delete(NotProcessedDirectory + CurrentFile.Name);
// Copy the file to the NotProcessed folder and delete the original
CurrentFile.CopyTo(NotProcessedDirectory + CurrentFile.Name);
CurrentFile.Delete();
bool FileAMessage = false;
Dts.Events.FireInformation(0, "FileA File Found", "File: " + CurrentFile.Name.ToString() + " moved to NotProcessed", string.Empty, 0, ref FileAMessage);
}
// If the file isn't an FileA, we want to get all Shipment Information files
else
{
if (CurrentFile.Name.Substring(0, 6).ToString().ToUpper() == "FILEB")
{
// Add each filename into an array which will populate our package object variable
ShipmentInformationCollection[InfoIndex] = CurrentFile.ToString();
InfoIndex++;
}
}
} // End ForEach File
// Add all of our FileA file names to our Ophan File object
if (FileAIndex > 0)
{
bool AddingFileAMessage = false;
Dts.Events.FireInformation(0, "Adding FileA Files to Collection", FileAIndex + " file(s) added to collection", string.Empty, 0, ref AddingFileAMessage);
Dts.Variables["FileARecordCount"].Value = FileAIndex;
Dts.Variables["FileAFileCollection"].Value = FileAFileCollection;
}
// Add all of our Shipment Information file names to our Shipment Information Object
if (InfoIndex > 0)
{
Dts.Variables["ShipmentInformationCollection"].Value = ShipmentInformationCollection;
}
} //End Main
在此脚本任务结束后，我将转到使用带有 ObjectVariableA 的 ADO 集合作为其集合的每个容器，将所述变量的当前值传递到字符串变量 FileName 中.澄清一下，我正在使用脚本任务将一堆文件名放入我的对象中，这些文件名类型为A"，并循环遍历每个文件以继续我的逻辑.
After this script task ends, I am going to a for each container that uses an ADO collection with ObjectVariableA as its collection, passing the current value of said variable into a string varable, FileName. To clarify, I'm using the script task to get a bunch of file names into my object that are of type "A" and loop through each file to continue my logic.
非常感谢任何帮助.感谢您的关注！
Any help is greatly appreciated. Thank you for looking!
推荐答案
您似乎正在尝试在 SSIS 变量中添加/连接值.由于多种原因，这不会起作用.
It appears you are attempting to add/concatenate values in an SSIS Variable. That's not going to work for a number of reasons.
第一个原因是 SSIS 变量的数据类型大致类似于 .NET 原语.因此， += 不会做你认为它会做的事情(假设它不会彻底爆炸).
The first reason, is that the data types of SSIS Variables are roughly analogous to the .NET primitives. Therefore, the += isn't going to do what you think it will do (assuming it doesn't blow up outright).
第二个原因是您正在对基础对象本身进行操作.相反，您可能希望分配给 .Value 属性.这就是在 ForEach 循环构造中自动访问的内容.
The second reason is that you are operating on the base Object itself. Instead, you're likely looking to assign to the .Value property. That is what would be automagically accessed in a ForEach loop construct.
// illogical for SSIS
for(int i = 0; i < fileAIndex; i++)
Dts.Variables["ObjectVariableA"] += fileA[i].toString();
// Replaced with
Dts.Variables["ObjectVariableA"].Value = fileA;
只需将类似对象的数组分配给对象类型的 SSIS 变量.Object 可以保存从 object 派生的任何东西.有一个数据集，将它分配给一个对象.您自己的自定义类，相同.
Just assign the array like object to an SSIS Variable of type Object. The Object can hold anything that's derived from object. Have a DataSet, assign it to an Object. Your own custom class, same.
这解决了您问题的技术部分.我强烈建议如果你能解释一下你在做什么，我们可以找到一种更多的 SSIS-ic(与 pythonic 的影响不太一样)的做法东西.
That addresses the technical part of your question. I strongly suggest if you would explain what you're doing, we could find a more SSIS-ic (doesn't quite have the same impact of pythonic) way of doing things.
这篇关于SSIS 脚本任务:从数组填充对象变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
