vb script and sql server 2000 dts

[sarahw]

I am trying to write a script that effectively removes timestamp values, so that I can import data into sql server 2000 using dts. I have post this on the sql server site - to no avail, so thought I might try a different approach. Could anyone tell me what is wrong with the following code - it errors at line 1.

Function Main()
Dim IDate as variant,
Dim ITime as variant,
Dim IDateTime as variant
On error resume next ' Just keep on going
IDate=DTSSource("Date&quot
ITime=DTSSource("Time&quot
if isnull(IDate) then IDate = "01/01/1900"
if isnull(ITime) then ITime = "00:00:01"
if not isdate(IDate) and ITime = "00:00:01" then ' Looks like the Time is in the date field
ITime = IDate
IDate = "01/01/1900"
endif
if not isdate(IDate) then Idate = "01/01/1900" ' Catch all
IDateTime = "'" & trim(IDate) & " " & trim(ITime) & "'"
DTSDestination("IDateTime&quot
On error goto 0 ' Resume error trapping
Main = DTSTransformStat_OK
End Function

All responses are very much appreciated
Sarah

 

[link9]

DTSDestination("IDateTime&quot

Main = DTSTransformStat_OK

I may be inexperienced with DTS (I've used it, but I'm no expert) -- but I don't see what you're doing with that first line -- what is DTSDestination, and what is supposed to happen on that line?? It's like you are just saying something for the sake of saying it...

Then I'm assuming that "DTSTransformStat_OK" is some sort of constant that you will be checking for when the function returns the value???

What is is supposed to return if everything doesn't go ok???

To me, it appears that no matter what happens in the function, you are just going to return that value --

Please elaborate, and we might be able to help.

Paul Prewett

 

[jkb17]

try getting rid of the "dim idate as variant" and just type
"dim idate"

 

[sarahw]

thanks jkb17 - the code seems to get a bit further now ie the error at line 12. Any further thoughts?

link9 - the DTSDestination("IDateTime&quotis telling sqlserver to create a destination column called IDateTime. IDateTime should be the combination of the IDate and ITime values. Not sure what DTSTransformStat_OK does - it's part of the automatically generated code.

Thankyou both for responding and if you have any more ideas I will be very grateful.

Sarah