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

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: