Hi folks...
I'm bringing in Data from a Terradata Warehouse to a SQL server table
it's a table with 4 cols cols two of which are dates.
I worked out that the following dates were breaking SQL server validation
0001-01-01
0004-04-01
etc...
clearly these are not dates...
so I did this in the Transformation ActiveX script
my aim was...every time you get a date that is nonsensical...replace it with
Jan-10-1900 this will work fine in a smalldatetime field.
' Copy each source column to the destination column
Function Main()
dim myDate
mydate = cdate(#01/10/1900#)
If DTSSource("UPD_DT") < mydate then
msgbox "less than=" & cdate(mydate),vbInformation
DTSDestination("UPD_DT") = cdate(mydate)
msgbox "done less than=" & cdate(mydate),vbInformation
Else
DTSDestination("UPD_DT") = DTSSource("UPD_DT")
msgbox "more than=" & DTSSource("UPD_DT")
,vbInformation
End if
DTSDestination("SVV_DT") = DTSSource("SVV_DT")
DTSDestination("STA_CD") = DTSSource("STA_CD")
DTSDestination("PTY_ID") = DTSSource("PTY_ID")
msgbox "done the lot=" & DTSTransformStat_OK,vbInformation
Main = DTSTransformStat_OK
End Function
Explanation:
I've put msgboxes in to do some elementary debugging. They come up once when
the date is the year 200 (I know!) and I get all the msgboxes firing OK (the
else msgbox never fires) and then the last one is the one with the status
variable which says "done the lot=1" so the outgoing status var is 1 which
seems good to me, 1 is true no?
I'm happy to clarify any of this, but I would really appreciate some help on
how to deal with crappy dates. The incoming format seems to be dd/mm/yyyy and
the test of 'is it less than Jan 10 1900?' appears to be working for the bad
date, so why won't it accept the replacement of the bad date with a 1900
date??
Regards and thanks in advance
Charles
