none
Reading a Recordset multiple times gives 0 rows

    Question

  • Hi

    I have an EXECUTE SQL Task, which gets a result-set from SQLServer using OLEDB Connection.

    The result set is mapped to an object variable , say @[User::FilePath]

    There are 33 row is the above resultset.

    Then, I have a For-each loop, inside which, I have a Script task .

    I am trying to put the above @[User::FilePath] recordset into a DataTable using DataAdapter.Fill() function. I perform some read operations to its rows.

    The problem is , in the First Iteration of For-Each-Loop, the number of rows in data-table shows 33.

    But from the Next Iteration, it comes out to be 0. (ZERO!!)

    This causes my package to fail.

    Has anybody faced such an issue earlier ??

    Any solutions or suggestions are welcome.....


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, October 26, 2013 12:14 PM

Answers

  • Hi All,

    I went through a lot of blogs , and came to know that Recordset in SSIS behaves like a One time Fill variable.

    If we fill this recordset into a DataTable, it can not be used again. This is a common problem all across.

    :

    I found a work-around for this 

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

    This is now working!!

    Thanks to All for their Time!!


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Ashu_Blueray Sunday, October 27, 2013 11:03 AM
    Sunday, October 27, 2013 11:03 AM

All replies