I'm using the following script to transform my source (a fixed field
text file) from yyyyMMdd to a normal datetime field in my destination
table.

Function Main()

dim i_Day
dim i_Month
dim i_Year

i_Day = Cint(Mid( DTSSource("Col006") ,7 , 2 ))
i_Month = Cint(Mid( DTSSource("Col006") ,5 , 2 ))
i_Year = Cint(Left(DTSSource("Col006"),4))

DTSDestination("DATISS") = DateSerial( i_Year , i_Month ,i_Day )
Main = DTSTransformStat_OK
End Function

This works on several of my DTS imports, but on one there are several
fields that fail and it excedes the error limit. Is there some way i
can tell the DTS job to just skip rows that fail instead of having the
whole import fail? There are only ~32 rows out of 40,000 that are non
conforming but it causes the whole job to fail.
The dates are being manually entered into the system that provides my
source file, so human error is the cause for the incorect dates like
02004121.

My experience with programming is not extensive but I do learn well by
example. I don't mind reading and researching, I just wasn't sure where
to begin.

thanks in advance,
brian

If it is in the format of YYYYMMDD then why do ay transformation at all?
That is a perfect format for SQL Server to convert from a string to a
datetime where it will not mistake the format. This of coarse does not help
if the date is typed wrong. If this is the only column you are transforming
I would use TSql and something like this:

SELECT CASE WHEN ISDATE(YourCol) = 1 THEN YourCol ELSE NULL END FROM
YourTable


--
Andrew J. Kelly SQL MVP


<deanbri75 (AT) hotmail (DOT) com> wrote

Andrew J. Kelly wrote:
I first tried doing it without the ActiveX transformation. However
when going from source to destination in my DTS job, I tried both
Datetime string and copy column and got the following errors.

Datetime string error:
TransofrmDateTimeString'DTSTransformation_7', column pair 1 (source
dolumn 'Col006' (DBTYPE_STR), destination col'DATISS'
(DBTYPE_DBTIMESTAMP)): Cannot parse input data string beginning at
'20040812'

Copy column error:
schema validation failed

I have several DTS jobs that import fiext field text files with dates
in this format Using the activeX transform is the only way I've got it
to import and have SQL recognize it as a date. I'm just having a
particular issue in this instance due to the non-conforming data that
causes the import to fail.

If this is going to a staging table then you can import it as a string and
do the validation and conversion there.

--
Andrew J. Kelly SQL MVP


<deanbri75 (AT) hotmail (DOT) com> wrote

I would check first in your script that the date length is that expected
and reject if not (assuming dates are entered in full). If dates not
entered in full, then you can check boundaries, for example "02004121"
has 8 characters, a perfectly sized string should have seven.

I would drop the integer part and keep the 2 character day and month
(i.e. "2004" "04" "21") as integer translation is unnecessary.

Finally, once you have performed your own manipulation, run the results
past IsDate() to see if it is a valid date, if not return a null. This
will stop your package from crashing.

Hope this helps.



deanbri75 (AT) hotmail (DOT) com wrote: