4

I have a package, inside which I have two components: First is a SQL task to execute and get an ADO result set, the second one is a script task, I convert the result set to System.Data.DataTable twice, something like that:

 System.Data.DataTable t1= new System.Data.DataTable();
 OleDbDataAdapter adp = new OleDbDataAdapter();
 adp.Fill(t1, Dts.Variables["ResultSet"].Value);

 System.Data.DataTable t2= new System.Data.DataTable();
 OleDbDataAdapter adp2 = new OleDbDataAdapter();
 adp2.Fill(t2, Dts.Variables["ResultSet"].Value);

The result is t1 is correctly filled, but t2 remains empty; what's more, I have explicitly set [User::ResultSet] as a read only variable, it seems the variable just become emptied, even I add one more script task later and do the same thing again, the filled DataTableare still empty.

There are many ways to circumvent my case so I don't expect a solution here. But I want to get clarify those things: Does OleDbDataAdapter.Fill have side effects to the original datasets, and how is my read-only variable changed its value?

Hadi
  • 36,233
  • 13
  • 65
  • 124
a4194304
  • 366
  • 2
  • 13

2 Answers2

3

I don't think this is altering the Read-Only Recordset variable value, the issue is that when using Adapter.Fill method to fill a datatable with a recordset, the Record set is still open you have to close it explicitly to be able to use it again with other adapters. (When you try to read from it again it is finished providing rows, so you are trying to read from the end)

From the following Microsoft articles:

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.

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.

I don't really find how to close SSIS recordset after using Fill method, but i find a link for a similar issue with a workaround:

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

Fill uses a pointer to know where in the source dataset it is reading from. After filling the first time, you are at the end of the variable dataset. Assigning the DTS variable to a script variable first:

var d = Dts.Variables["ResultSet"].Value.ToList();

should let you use it multiple times. See DataReader cursor rewind‌

Randy Slavey
  • 544
  • 4
  • 19