Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 20th, 2007, 05:08 AM
Registered User
 
Join Date: Sep 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Many Destination while using One Source

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~

 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SSIS XML as a destination? GethWho BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 5 February 3rd, 2016 07:22 AM
SSIS Package:RawFile Source,FlatFile Destination Mit_2807 BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 0 September 19th, 2008 04:01 AM
Query must have at least one destination field Spydermine Access 2 May 3rd, 2007 07:47 AM
Script Component as Destination bmains SQL Server 2000 0 May 23rd, 2005 01:30 PM
Update Source Record After Insert Duncan Allen SQL Server DTS 1 October 25th, 2004 10:44 AM



All times are GMT -4. The time now is 06:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.