I am working on bringing data into our SQL Server from a btreive
database through Pervasive.
It seems to work, mostly, but I am having problems with dates.
At first, all date conversions from btreive field type Date to SQL
Server SmallDateTime would produce nothing but errors of this sort:
ActiveX Scripting Transform 'DTSTransformation_1' encountered and
invalid data value for 'HomesteadDate' source column.
Looking into the data on the pervasive side, many of the the date
fields had no data in them.
I tried to get around this by changing all lines that brought over
dates from this:
DTSDestination("HomesteadDate") = DTSSource("HomesteadDate")
to this:
IF ISDATE(DTSSource("HomesteadDate")) = 1 THEN
DTSDestination("HomesteadDate") = DTSSource("HomesteadDate") ELSE
DTSDestination("HomesteadDate") = NULL
What I have discovered since then, is that all my dates are simply
failing the ISDATE check and returning null to the SQL Server, even
when it is a valid date.
Any suggestions would be greatly appreciated.
Steve Hoyer
GPW, MI