2

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?

Improve this question
4
  • What method, i.e. PreExecute, Input0_ProcessInputRow, PostExecute, is the code in? Commented Mar 22, 2019 at 14:16
  • @userfl89 what is mentioned in the answer is correct. The problem is that the object variable contains a Recordset when it is consumed using Fill() method it needs to be close and reopened or it will return empty result when executed multiple time. It is not related to the method where the code is executed Commented Mar 22, 2019 at 15:35
  • 1
    @Yahfoufi I see, that makes sense. Thanks Commented Mar 22, 2019 at 15:43
  • Check my answer update i added a new possible solution Commented Mar 22, 2019 at 17:27

2 Answers 2

Reset to default
2

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

(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:

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Hi Hadi, thanks for all the work you have been putting into this issue. Using your 3rd solution I am at least presented a workable error System.Runtime.InteropServices.COMException: 'Operation is not allowed when the object is closed.'
@Caz1224 why not trying the first approach, i faced a similar issue from a while and i was able to solve it using this approach. regarding to the third approach i tried it with a recordset retrieved using a connection, i didn't really tried it using an object variable.
@Caz1224 concerning the third approach, try removing the rs.Close() command from the first Script and in the second script add Rs.MoveFirst() before start reading from the object variable
I ended up going a different way. The ADO method was just causing too much pain. What I ended up doing was a TO JSON SQL query, saving that as an object, converting it to a string and then deserializing it as my list in the script component
Method #1 worked for me! Never would have thought it could be so simple.
|
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.