Problem with DTS and Bad Dates

found the solution:

Function Main()
Dim sYear, sMonth, sDay, sDate
sYear = Left( DTSSource("Col002"),4)
sMonth = right( DTSSource("Col002"),2)
sDay = mid(DTSSource("Col002"),5,2)
sDate = sYear & "-" & sMonth & "-" & sDay
If IsDate(sDate) Then
DTSDestination("Col002") = sDate
Else
DTSDestination("Col002") = Null
End If
Main = DTSTransformStat_OK
End Function



robboll wrote:
> I have a DTS routine that I have been using to append dates into a SQL
> Server 2000 table and it usually works great. It appends to a
> Smalldatetime column -- It is as follows:
>
> '**********************************************************************
> ' Visual Basic Transformation Script
> ' Copy each source column to the
> ' destination column
> '************************************************************************
>
> Function Main()
> 'DTSDestination("Col002") = DTSSource("Col002")
> if DTSSource("Col002") = "99999999" or DTSSource("Col002") = Null then
>
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/" &
> mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)
> End if
> Main = DTSTransformStat_OK
> End Function
>
> The problem is when it hits a bad date such as: 19910631
>
> Instead of ignoring the bad date the entire DTS job fails. Obviously
> this is something that should be validated at data entry, but
> unfortunately the only control I have is when appending to the table.
> Any suggestions as to what I can add to the code to account for bad
> date entries. Thank in advance.
>
> RBollinger