activex question 1

[swoodring]

I'm attempting to write my first activex script. Not familiar with VB script so bear with what might seem like a simple question.

I'm trying to use an activex script in a dts to do a transformation. I have all the columns as straight copies except for one which I'm doing an activex script. What I'm trying to do is if the date is less than 1900-01-01 I want to default it to 1900-01-01. Every once in awhile a date comes across from the source file with a date of 0001-01-01 which sql doesn't like so I want to default it to 1900-01-01 instead. The script I've created parces fine but when I run the dts I get an error that the activex script encountered an invalid data value for source column LLAI. I don't understand why. any suggestions would be greatly appreciated.

Here is the script.

Function Main()
If DTSSource("LLAI&quot >= "1900-01-01" Then
DTSDestination("LastActivityISODate&quot = DTSSource("LLAI&quot
Else
DTSDestination("LastActivityISODate&quot = "1900-01-01"
End If

Main = DTSTransformStat_OK

End Function

 

[TomSark]

What's the datatype for the LLAI column?

Remember, in VBScript, everything is a variant. So depending on it's type, your could user CStr(DTSSource("LLAI&quot ) to convert it to a string ot CDate(DTSSource("LLAI&quot ) to convert it to a date as in:

IF CStr(DTSSource("LLAI&quot ) >= "1900-01-01"... or whatever...

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[swoodring]

Field LLAI is coming from a file on our as/400 and it is a datetype filed stored in the format 2001-01-01 but as I was saying I found this one instance of 0001-01-01 which is the default date on the as/400 for datetype fields.

 

[jballum]

Have you tried IsDate("LLAI&quot?

JB

 

[cdukes]

The main problem is that SQL Server will not recognise 0001-01-01 as a date. try converting to a string and taking the first 4 characters and test them:

if cint(left(cstr(DTSSource("LLAI&quot), 4)) >= 1900 then

Hope this helps

Chris Dukes