SSIS 2012-执行第二个脚本时变量为空 [英] SSIS 2012 - Variable empty on second script execution
问题描述
我在数据流中进行了脚本组件转换. 在此脚本组件中，我从对象变量中读取了一个表.通过脚本的第一条记录可以正常工作.该变量可以正确读取并完美加载到列表对象中.
I have a script component transformation in a data flow. In this script component I read a table from a object variable. The first record that passes through the script works fine. The variable reads correctly and loads into a list object perfectly.
下一条记录传递到脚本中，出现了问题.
The next record passes into the script something goes wrong.
查看该变量，它的记录计数为44，当尝试将其加载到我的列表中时，我得到的行数= 0
Looking at the variable it reports a Record Count of 44, when it attempts to load into my list I get a rowcount = 0
下面是加载列表的脚本
List<PublicHoliday> PublicHolidays = new List<PublicHoliday>();
OleDbDataAdapter A = new OleDbDataAdapter();
DataTable dt = new DataTable();
A.Fill(dt, Variables.LISTPublicHolidays);
foreach (DataRow row in dt.Rows)
{
object[] array = row.ItemArray;
var Public = new PublicHoliday()
{
DateKey = int.Parse(array[0].ToString()),
FullDateAlternateKey = DateTime.Parse(array[1].ToString())
};
PublicHolidays.Add(Public);
}
我错过了什么吗?有人遇到过这个问题吗?
Am I missing something? Has anyone come across this issue before?
推荐答案
试图找出问题所在
从以下 OleDbDataAdapter.Fill方法( DataTable，Object)文档:
注意
当将ADO Recordset或Record对象与.NET Framework应用程序结合使用时，请在完成后始终调用Close.这样可以确保及时释放与数据源的基础连接，还可以防止在现有引用仍然存在时，由于垃圾回收回收非托管ADO对象而可能导致的访问冲突.
CAUTION
When using ADO Recordset or Record objects in conjunction with .NET Framework applications, always call Close when you are finished. This ensures that the underlying connection to a data source is released in a timely manner, and also prevents possible access violations due to unmanaged ADO objects being reclaimed by garbage collection when existing references still exist.
请注意，当Fill操作完成时，采用DataSet和ADO对象的OleDbDataAdapter.Fill重载会隐式调用ADO对象上的Close.您需要在调用接受DataTable的OleDbDataAdapter.Fill重载之后，显式关闭ADO Recordset或Record对象.
Note that the OleDbDataAdapter.Fill overload that takes a DataSet and an ADO object implicitly calls Close on the ADO object when the Fill operation is complete. You need to explicitly close the ADO Recordset or Record object after calling the OleDbDataAdapter.Fill overload that takes a DataTable.
这意味着在RecordSet上调用Fill Method时，必须先将其关闭，然后才能再次使用它，否则将不返回任何行.
我真的不知道如何从Object变量中关闭Recordset，但是我将尝试提供一些可能的解决方法:
I don't know really how to close a Recordset from Object variable, but i will try to provide some possible workarounds:
在下面的链接中，他们提到了以下解决方法:
In the link below they mentioned the following workaround:
-
在我的记录集
@[User::FilePath]填充后，我就使用了脚本任务，并使用OledbDataAdapter和 数据表.
As soon as my Recordset
@[User::FilePath]gets populated, I use a Script Task , and Fill it into a DataSet ds using OledbDataAdapter and DataTable.
然后，在同一脚本任务中，将ds的值放到新的
对象类型@[User::FilePathDataTable]的变量.
Then, in the same script task, I put the value of ds to a new
variable of Object Type @[User::FilePathDataTable].
通过执行此操作，FilePathDataTable的数据类型将变为
System.Data.DataTable.
By doing this, the DataType of FilePathDataTable becomes
System.Data.DataTable.
该数据表可以轻松地在内部使用任意次数 每次循环.
This datatable can easily be used any number of times inside the For-Each Loop.
我不在sis的ForEach循环内使用DataAdapter.Fill()方法
现在.我只是将@[User::FilePathDataTable]的值分配给新
数据集，并将其用于迭代.
I don't use DataAdapter.Fill() method inside ForEach Loop of ssis
now. I just assign the Value of @[User::FilePathDataTable] to a new
dataset, and use it for the iterations.
参考
- Reading a Recordset multiple times gives 0 rows
- How to reuse a Recordset Object in SSIS
与其使用脚本组件从Object变量生成行，不如尝试使用RecordSet Source来做到这一点.
Instead of using Script Component to generate rows from Object variable try using RecordSet Source to do that.
(3)将变量强制转换为Recordset
我没有测试这种方法，而且不确定是否可以与对象变量一起使用
它需要对Microsoft ActiveX数据对象的引用.
It requires a reference to Microsoft ActiveX Data Objects.
List<PublicHoliday> PublicHolidays = new List<PublicHoliday>();
var rs = ((ADODB.Recordset)Variables.LISTPublicHolidays);
OleDbDataAdapter A = new OleDbDataAdapter();
DataTable dt = new DataTable();
A.Fill(dt, rs);
foreach (DataRow row in dt.Rows)
{
object[] array = row.ItemArray;
var Public = new PublicHoliday()
{
DateKey = int.Parse(array[0].ToString()),
FullDateAlternateKey = DateTime.Parse(array[1].ToString())
};
PublicHolidays.Add(Public);
}
rs.Close();
更新1
基于下面的评论，请尝试从第一个脚本中删除rs.Close();方法，并在第二个脚本中删除第二个脚本，然后再执行Fill方法，使用rs.MoverFirst()方法能够从记录集中检索信息.
based on the comments below try removing rs.Close(); method from the first script and in the second script before executing Fill method use rs.MoverFirst() method to be able to retrieve information from recordset.
基于以下链接删除了第三种方法:
