SSIS - Use a Object variable (Recordset) in Script Task: only one time

Question

I've a Curious problem with Recordset and Script Component, so I'm going to explain the situation.

In my first step, I've a Data Flow Task where I read one table from SQL and store the result in a Recordset Destination, and save this Recordset in a variable.

In my second step, I've a Data Flow Task with this component:

• a - I Load a .csv file with File Flat Source
• b - I modify data with Script Component
• c - I save data in SQL with OLE DB Destination

In step b I rise an event for each row and I catch this event in ad EventHandler with a Script Task The problem occurs in this Script Task. This is the code:

public void Main()
{
    ...
    string messaggio = GetMessaggio(connection, EventNumber);
    ...
}

private string GetMessaggio(string eventNumber)
{
    using (var adapt = new OleDbDataAdapter())
    using (var dt = new DataTable())
    {
        adapt.Fill(dt, Dts.Variables["User::RS_EventType"].Value);
        return dt.AsEnumerable()
                    .Where(r => r.Field<int>("ev_number").ToString() == eventNumber)
                    .Select(r => r.Field<string>("ev_message"))
                    .FirstOrDefault() ?? "ND";
    }
}

First time, datatable are loaded with correct records, but this occurs only first time. When method is called again, Dts.Variables["User::RS_EventType"].Value seems still be valid, but adapt.Fill do not fill the datatable.

What could be the problem?

Answer 1

I found the solution reading this post: SSIS Script Task - Reading RecordSet object in a loop is failing

now this is the code:

private string GetMessaggio(string eventNumber)
{
    using (var adapt = new OleDbDataAdapter())
    using (var dt = new DataTable())
    {
        var stm = new ADODB.Stream();
        var rsCopy = new ADODB.Recordset();
        var rs = ((ADODB.Recordset)Dts.Variables["User::RS_EventType"].Value).Clone();

        rs.Save(stm);
        rsCopy.Open(stm);

        adapt.Fill(dt, rsCopy);

        rs.Close();
        stm.Close();
        rsCopy.Close();
        Marshal.ReleaseComObject(stm);
        Marshal.ReleaseComObject(rsCopy);
        Marshal.ReleaseComObject(rs);

        return dt.AsEnumerable()
                    .Where(r => r.Field<int>("ev_number").ToString() == eventNumber)
                    .Select(r => r.Field<string>("ev_message"))
                    .FirstOrDefault() ?? "ND";
    }
}