I trying to the DTS Transformation task and ActiveX Script(VB) to get Start and End Date from a source and convert it into days of the week and insert into destination e.g. 10 June(Start date) Mon to 15 June (End date) Fri, column Mon - Fri will be set to value of 1 and sat and sun will be set to 0. Each row in the destination will represent one week. The codes I'm currenly using can do a week no problem, but I am not sure how to do it for dates running over more than a week. e.g 10 June (start date) to 28 june(end date)? Like how can I use a single row from source to update the many rows in destination, and then move to the next source row and repeat again? I also have the another transformation which simply uses 'copy column' username (from same source) to the destination table.

Any help will be much appreciated, sample codes are welcomed!

HERE IS THE CODE I AM USING:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
' Copy each source column to the destination column
Function Main()

             wc = WKbegin(DTSSource("StartDate"))
         DTSDestination("WeekCommencing") = wc

        startdate = DTSSource("StartDate")
        enddate = DTSSource("EndDate")


        DTSDestination("Mon") = Fillday(wc, startdate, enddate, 0)
         DTSDestination("Tues") = Fillday(wc, startdate, enddate, 1)
         DTSDestination("Wed") = Fillday(wc, startdate, enddate, 2)
         DTSDestination("Thurs") = Fillday(wc, startdate, enddate,3)
         DTSDestination("Fri") = Fillday(wc, startdate, enddate, 4)
         DTSDestination("Sat") = Fillday(wc, startdate, enddate, 5)
         DTSDestination("Sun") = Fillday(wc, startdate, enddate, 6)
    Main = DTSTransformStat_OK

End Function

'Convert dates into days of the week
Function Fillday(wc, startdate, enddate, day)

   Select Case day
     Case 0
       If startdate = wc Then
          Fillday = 1
       Else
          Fillday = 0
       End If

     Case 1
       If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

    Case 2
     If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

    Case 3
      If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

    Case 4
        If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

    Case 5
        If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

     Case 6
       If startdate = wc+day OR (startdate < wc+day AND wc+day < enddate) OR enddate = wc+day Then
          Fillday = 1
       Else
          Fillday = 0
       End If

 End Select

End Function


' Check Week Commencing for a date given
Function WKbegin(gDate)

            'check if sunday
           If CInt(weekday (gDate) ) = 1 Then
              gDate = gDate -1
           End If

            ' work out Monday date
               WKbegin = DateAdd("d", 2 - weekday (gDate) , gDate)

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~