Re: Date Transformation from YYYYMMDD - yet another question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Collecting output generated via one spreadsheet into a new spreasheet
    ... A quick read of the script indicates that it makes no attempt to actually write out any of the information is has found. ... 'Call Output sub ... I would hazard a guess that if the output contains only the info associated with the last record from the input file, that you are failing to increment the row counter associated with the output spreadsheet after writing to it. ... Dim strUserName, objUserDomain, objGroup, objUser, strGroupList ...
    (microsoft.public.scripting.vbscript)