sql server dts: ActiveX code to handle 'null' dates -- sql server dts

ActiveX code to handle 'null' dates

Phil Cook
3/31/2005 8:05:03 AM

sql server dts:

I am re-writing an old BCP job using DTS. The job imports a fixed length text
file which includes date fields in the format yyyymmdd. Sometimes there will
be no valid date on a record and this is held as '00000000' in the text file.
This should be moved into the database as a NULL date value. I am trying to
handle this situation with an ActiveX code transformation but my knowledge of
ActiveX is very limited. Can anyone provide any guidance?

Re: ActiveX code to handle 'null' dates

Allan Mitchell
3/31/2005 6:06:28 PM

I would always move text files to a staging table from which I could
then do

NULLIF(<column Name>,'00000000')

Here is another idea

Function Main()

dim y,m,d

y = left( DTSSource("col1"),4)
d = right( DTSSource("col1"),2)
m = mid( DTSSource("col1"),3,2)

Msgbox DTSSource("col1")

if DTSSource("col1") = "00000000" then

else
DTSDestination("col1") = DateSerial(y ,m , d )
End if
Main = DTSTransformStat_OK
End Function

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.