"B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
news:1CCA82DF-B3DA-425C-B758-55882FF39622@microsoft.com...
>I am trying to create a dts package to copy a FoxPro 6 table to SQL 2000.
>I
> seem to be having problems with the datetime fields. .....
> There do appear to be a number of null date records in the source table
> .....

> I am trying to create a dts package to copy a FoxPro 6 table to SQL
> 2000. I seem to be having problems with the datetime fields. The
> usual error is something like this: Insert Error, column 22('<column
> name>, DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid
> character value for cast specification.
>
> There do appear to be a number of null date records in the source
> table and what I have been trying to do is write a script to allow for
> this. I have made all datetime fields in the destination table
> nullable and I have tried to modify the transformation script for the
> date fields as follows:
>
> if isnull(DTSSource("bdte").value) then
> DTSDestination("bdte") = null
> Else
> DTSDestination("bdte") = DTSSource("bdte")
> End if
> Any suggestions? (Admittedly I have not worked all that much with DTS
> or VBScript.)
>

"Allan Mitchell" wrote:

> Hello B. Chernick,
>
> Are you sure that is the problem? It could well be
>
> 1. The values from the Foxpro side are simply not convertible to the SQL
> Server version even if they look right in Foxpro
> 2. You have junk data in the Foxpro column
>
> Here is where we do a conversion to handle dates in DTS from char data
>
> http://www.sqldts.com/249.aspx
>
> --
>
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
> > I am trying to create a dts package to copy a FoxPro 6 table to SQL
> > 2000. I seem to be having problems with the datetime fields. The
> > usual error is something like this: Insert Error, column 22('<column
> > name>, DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid
> > character value for cast specification.
> >
> > There do appear to be a number of null date records in the source
> > table and what I have been trying to do is write a script to allow for
> > this. I have made all datetime fields in the destination table
> > nullable and I have tried to modify the transformation script for the
> > date fields as follows:
> >
> > if isnull(DTSSource("bdte").value) then
> > DTSDestination("bdte") = null
> > Else
> > DTSDestination("bdte") = DTSSource("bdte")
> > End if
> > Any suggestions? (Admittedly I have not worked all that much with DTS
> > or VBScript.)
> >
>
>