SSIS 2012 - Variable empty on second script execution

Question

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.

Looking at the variable it reports a Record Count of 44, when it attempts to load into my list I get a rowcount = 0

Below is the script that loads the list

    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?

Answer 1

Trying to figure out the issue

From the following OleDbDataAdapter.Fill Method (DataTable, Object) documentation:

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.

Also referring to Filling a DataSet with an ADO Recordset or Record - documentation:

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.

Which implies that when calling Fill Method over a RecordSet you must close it before using it a second time or no rows will be returned.

---

Possible Workarounds

I don't know really how to close a Recordset from Object variable, but i will try to provide some possible workarounds:

(1) Saving to DataTable

In the link below they mentioned the following workaround:

1. 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.

2. Then, in the same script task, I put the value of ds to a new variable of Object Type @[User::FilePathDataTable].

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.

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.

Reference

• Reading a Recordset multiple times gives 0 rows
• How to reuse a Recordset Object in SSIS

(2) Using Recordset Source

Instead of using Script Component to generate rows from Object variable try using RecordSet Source to do that.

(3) Cast variable as Recordset

I didn't tested this approach and I am not sure if it will works with object variable

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();

Update 1

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.

Third approach removed based on the following link:

• MoveFirst, MoveLast, MoveNext, and MovePrevious Methods (ADO)

Answer 2

You should use ADO NET instead of Ole Db connection:

Dim dt As Data.DataTable
Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet)
dt = ds.Tables(0)

Reference: https://www.sqlshack.com/execute-sql-tasks-in-ssis-output-parameters-vs-result-sets