Script Task – Read / Reuse same Recordset twice in a SSIS yields no records or empty

When using a script task/component in SSIS, we often try to read the data from the recordset using an OLEDBAdapter and fill the dataset, like below :

    DataTable dtValidatedResultset = new DataTable();

    OleDbDataAdapter adapter = new OleDbDataAdapter();

    adapter.Fill(dtValidatedResultset, Dts.Variables["User::InputData"].Value);

(The above code uses Script Task and hence it accesses the variable using Dts.Variables collection.  However if you were to use a Script Component, then you would use the strongly typed Variables collection object and access it as Variables.InputData).

However when you use consequtive Script Tasks or Script Components that try to access and read data from the SAME RECORDSET object, the first time it will work fine, but the second time it will read empty records.  This behavior is due to the fact that the Recordset is an ADODB object and has a record pointer / CURSOR that helps to navigate/move between the records.  Since the first time when the Dataset.Fill is invoked, it would have read all the records from the recordset and would have parked the record pointer at the last record in the recordset.

The next time when the same recordset is used in another Script Task/component, it will not rewind or take the recordset pointer to the first record, but would rather try to read it from its current EOR (End of  Record) position and hence you see your DataTable empty.  The only option is to CLONE the RECORDSET before using it again, which would ensure that the original recordset always stays intact for use any number of times in the package.

14,084 total views, 7 views today