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

When you import can you use a SQL Statement and not a table directly. You
could then do the comparison on the terradata side and substitute in the
right date before it even gets seen by the transformation.

Allan

"CharlesA" wrote:

Thanks Allan,
that's a very good point and quite true...however the SQL of Terradata is
arcane, and somewhat incomprehensible (with no help file) and little Internet
coverage. It complains about all sorts of standard constructions and I'm
clueless as to how to effect that change in Terradata, I was hoping to tackle
this in DTS as a way of getting round that deficiency, but this has turned
out to be rather opaque too!
Regards
CharlesA

You can still do it in DTS through an AX Transform but the logic on the
source statement should outperform this method.

In the AX trabsform I would look to check the first 4 chars of that attribute

I would then use this to output a DEFAULT date

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)



"CharlesA" wrote: