SSIS Recordset Access Via Script Task

Hi,



I have found that populating a data table from an SSIS variable (recordset) within a script task works fine first time round but produces no results subsequently:






Code Snippet

Public Class ScriptMain



Public Sub Main()



Dim ad As New OleDb.OleDbDataAdapter



Dim dt1 As New DataTable
ad.Fill(dt1, ReadVariable("myRecordset"))

'below returns count of 5500
System.Windows.Forms.MessageBox.Show("dt1 Count: " + dt1.Rows.Count.ToString)


Dim dt2 As New DataTable
ad.Fill(dt2, ReadVariable("myRecordset"))

'below returns count of 0
System.Windows.Forms.MessageBox.Show("dt2 Count: " + dt2.Rows.Count.ToString)

Dts.TaskResult = Dts.Results.Success



End Sub






Code Snippet

Private Function ReadVariable(ByVal varName As String) As Object

Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)

Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try

Catch ex As Exception
Throw ex
End Try

Return result

End Function





Anybody got any ideas?



Phil.