Re: Date Transformation from YYYYMMDD - yet another question

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Cagey (k_at_c.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 17:49:17 +0000

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@hotmail.com wrote:
> 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
>

Relevant Pages

  • Re: run function that is specified in a variable
    ... This could fail if error_Message and Error_action are not set up to accept zero-lenght strings. ... I created a table that specifies a process number and the script that is run to complete that process. ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Date Transformation from YYYYMMDD - yet another question
    ... dim i_Month ... fields that fail and it excedes the error limit. ... can tell the DTS job to just skip rows that fail instead of having the ...
    (microsoft.public.sqlserver.dts)
  • RE: export user accounts from NT 4.0 domain
    ... ATTENTION THE SCRIPT MUST BE RUNNED FROM A COMPUTER WHERE EXCEL IS ... from the information in a Microsoft Excel spreadsheet. ... Dim strLast, strFirst, strMiddle, strPW, intRow, intCol ... On Error GoTo 0 ...
    (microsoft.public.windows.server.scripting)
  • Re: Password Expire
    ... We have one fron end Edge server in our DMZ which passes email onto two ... I used to schedule a script to run every 24 hours on my Exchange 2003 ... Dim fso, txtarray, BodyText ... Call ProcessFolder (objContainer, numDays) ...
    (microsoft.public.exchange.admin)
  • Re: LDAP query information
    ... a "Dim" statement. ... execution of the script. ... ' Filter on computer object. ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)