title: SSIS recordset in script task: cannot use it with subsequent rows
body: <p>I am using a script task in SSIS in which I am using three different recordsets to add rows to the data flow.  Everything works well for the first use of these recordsets, but when additional rows need to access the recordsets, there is no data in them.</p>
  
  <p>What I am trying to do is to take these rows in the incoming data flow:</p>
  
  <pre><code>ID |   mScale   |  startDate  | End Date  ....
   1 |    w       |   7/8/13    | 10/31/13
   1 |    m       |   11/1/13   | 3/31/14
   1 |    q       |   4/1/14    | 7/31/14
   2 |    w       |   7/8/13    | 10/31/13
   2 |    m       |   11/1/13   | 3/31/14
   2 |    q       |   4/1/14    | 7/31/14
  </code></pre>
  
  <p>And add rows so the outgoing data flow looks like this:</p>
  
  <pre><code> ID  |  pScale   |   startDate  | EndDate
    1  |    w      |     7/8/13   |  7/14/13
    1  |    w      |     7/15/13  |  7/21/13
    ....
    1  |    w      |    10/28/13  |  10/31/13
    1  |    m      |    11/1/13   |  11/30/13
    1  |    m      |    12/1/13   |  12/31/13
    ...
    1  |    m      |    3/1/14    |  3/31/14
    1  |    q      |    4/1/14    |  6/30/14
    1  |    q      |    7/1/14    |  7/31/14
    2  |    w      |     7/8/13   |  7/14/13
    2  |    w      |     7/15/13  |  7/21/13
    ....
    2  |    w      |    10/28/13  |  10/31/13
    2  |    m      |    11/1/13   |  11/30/13
    2  |    m      |    12/1/13   |  12/31/13
    ...
    2  |    m      |    3/1/14    |  3/31/14
    2  |    q      |    4/1/14    |  6/30/14
    2  |    q      |    7/1/14    |  7/31/14
  </code></pre>
  
  <p>The recordsets contain the weekly, quarterly and monthly start and end dates.</p>
  
  <p>The output rows with the ID of 1 are being created, the output rows with the ID of 2 are not.</p>
  
  <p>I've found information on the internet that says that you can't iterate over the same recordset twice.  I'm wondering if there is a way to regenerate the recordset or reuse it somehow?  Or do I need to rethink this whole design?</p>
  
  <p>Thoughts appreciated, script below.</p>
  
  <pre><code>' Microsoft SQL Server Integration Services Script Component
  ' Write scripts using Microsoft Visual Basic 2008.
  ' ScriptMain is the entry point class of the script.
  
  Imports System
  Imports System.Data
  Imports System.Math
  Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
  Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
  Imports System.Xml
  Imports System.Data.OleDb
  
  
  &lt;Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()&gt; _
  &lt;CLSCompliant(False)&gt; _
  Public Class ScriptMain
  Inherits UserComponent
  
  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  
      Dim oleDA As New OleDbDataAdapter
      Dim dt As New DataTable
      Dim j As Integer
      Dim Difference As TimeSpan
  
      Try
          If Row.mScale = "w" Then
              'create 17 new rows, pull start date and enddate from the excel sheet.
              oleDA.Fill(dt, Me.ReadOnlyVariables("User::WeeklyData").Value)
              If dt.Rows.Count &gt; 0 Then
                  'loop through and find the proper start date,
                  j = 0
                  For Each dtRow As Data.DataRow In dt.Rows
  
                      Dim dtStartDate As String = dt.Rows(j)("StartDate").ToString
                      Dim dfStartDate As String = Row.oStartDate.ToString
  
                      If dfStartDate = dtStartDate Then
                          'start here to populate the next 17 rows.
                          Exit For
                      Else
                          j = j + 1
                      End If
                  Next
                  For i = 1 To 17
                      With Output0Buffer
                          .AddRow()
                          .PlanID = Row.PlanID
                          .oStartDate = dt.Rows.Item(j)(0).ToString
                          .aFID = Row.aFID
                          .oEndDate = dt.Rows.Item(j)(1).ToString
                          .pScale = Row.mScale
                          .pCount= 1
                          .nwDays = Weekdays(dt.Rows.Item(j)(0), dt.Rows.Item(j)(1))
                          .CreateDate = Today
                          .ModDate = Today
                          j = j + 1
                      End With
                  Next
              End If
          End If
  
          If Row.mScale = "m" Then
              'create 7 new rows, pull start date and enddate from the excel sheet.
              'where to start - the start of the month that is two months out from the project start date?
              'how to add two months to the date?
              oleDA.Fill(dt, Me.ReadOnlyVariables("User::MonthlyData").Value)
              If dt.Rows.Count &gt; 0 Then
                  'loop through and find the proper start date,
                  j = 0
                  For Each dtRow As Data.DataRow In dt.Rows
                      Dim dtStartDate As String = dt.Rows(j)("StartDate").ToString
                      Dim dfStartDate As String = Row.oStartDate.AddMonths(-2).ToString
                      'Subtract two months from start date.
  
                      If dfStartDate &lt;= dtStartDate Then
                          'start here to populate the next 7 rows.
                          Exit For
                      Else
                          j = j + 1
                      End If
                  Next
                  For i = 1 To 7
                      With Output0Buffer
                          .AddRow()
                          .PlanID = Row.PlanID
                          .oStartDate = dt.Rows.Item(j)(0).ToString
                          .aFID = Row.aFID
                          .oEndDate = dt.Rows.Item(j)(1).ToString
                          'need to store this in a variable to use for the start of the quarterly dates.
                          .pScale = Row.mScale
                          .pCount= 1
                          'Calculate .nwDays 
                          'NumWorkDays = dt.Rows.Item(j)(1).Subtract(dt.Rows.Item(j)(0).ToString)
                          '.nwDays = NumWorkDays.TotalDays
                          .nwDays = Weekdays(dt.Rows.Item(j)(0), dt.Rows.Item(j)(1))
                          .CreateDate = Today
                          .ModDate = Today
                          j = j + 1
                      End With
                  Next
              End If
          End If
  
          If Row.mScale = "q" Then
              'create x new rows, pull start date and enddate from the excel sheet.
              oleDA.Fill(dt, Me.ReadOnlyVariables("User::QuarterlyData").Value)
              If dt.Rows.Count &gt; 0 Then
                  'loop through and find the proper start date,
                  j = 0
                  For Each dtRow As Data.DataRow In dt.Rows
                      Dim dtStartDate As String = dt.Rows(j)("StartDate").ToString
  
                      If Row.oStartDate &lt;= dtStartDate Then
                          'start here to populate the next x rows until the project end date.
                          Exit For
                      Else
                          j = j + 1
                      End If
                  Next
  
                  While dt.Rows.Item(j)(0) &lt;= Row.UpdateAccIDprojEndDate
                      With Output0Buffer
                          .AddRow()
                          .PlanID = Row.PlanID
                          .oStartDate = dt.Rows.Item(j)(0).ToString
                          .aFID = Row.aFID
                          'IF THIS IS WITHIN THE QUARTER WE'RE ON, THEN POPULATE WITH PROJECT END DATE.
                          Difference = dt.Rows.Item(j)(1).Subtract(Row.UpdateAccIDprojEndDate)
  
  
                          If (Row.UpdateAccIDprojEndDate &lt; dt.Rows.Item(j)(1)) Then
                              .oEndDate = Row.UpdateAccIDprojEndDate
                              .nwDays = Weekdays(dt.Rows.Item(j)(0), Row.UpdateAccIDprojEndDate)
  
                          Else
                              .oEndDate = dt.Rows.Item(j)(1).ToString
                              .nwDays = Weekdays(dt.Rows.Item(j)(0), dt.Rows.Item(j)(1))
  
                          End If
                          .pScale = Row.mScale
                          .pCount= 1
                          .CreateDate = Today
                          .ModDate = Today
                          j = j + 1
                      End With
                  End While
              End If
          End If
  
      Catch ex As Exception
          Throw ex
      Finally
          'use this to do something even if the script task fails.
      End Try
  End Sub
  
  Private Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
      Dim numWeekdays As Integer
      Dim totalDays As Integer
      Dim WeekendDays As Integer
      numWeekdays = 0
      WeekendDays = 0
  
      totalDays = DateDiff(DateInterval.Day, startDate, endDate) + 1
  
      For i As Integer = 1 To totalDays
  
          If DatePart(DateInterval.Weekday, startDate) = 1 Then
              WeekendDays = WeekendDays + 1
          End If
          If DatePart(DateInterval.Weekday, startDate) = 7 Then
              WeekendDays = WeekendDays + 1
          End If
          startDate = DateAdd("d", 1, startDate)
      Next
  
      numWeekdays = totalDays - WeekendDays
  
      Return numWeekdays
  
  End Function
  
  
  End Class
  </code></pre>