Posted by Stu on 10/02/41 11:56

Regardless of how you want to handle the bad data, you need to check if
the parsed source column is a date first, eg:

Dim DestinationDate

DestinationDate = mid(DTSSource("Col002"),1,4) & "/" &
mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)

If IsDate(DestinationDate) is True then
DTSDestination("Col002") = DestinationDate
Else
'do something; write the row to a log, correct the error
based on predefined rules,
'skip the import
End if

HTH,
Stu


robboll wrote:
> This routine works in most cases, but fails when a bad date is entered
> such as:
> 19910631 -- there is no June 31st.
>
> 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
> with these data quirks. Any suggestions appreciated!!!
>
> '**********************************************************************
>
> ' 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
>
>
> RBollinger

• Next in forum: Re: When DTS hits a bad date
• Prev in forum: Re: Replication performance - how to measure it ?
• Thread view: Re: When DTS hits a bad date

[Reply to this message]