Importing DBFs using DTS wizard = error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing DBFs using DTS wizard = error

Author

Topic

Smoo
Starting Member

2 Posts

Posted - 2002-10-31 : 15:44:32

Hi, I've used the DTS wizard to create a package that will drop/create and load data into a table from a DBF file (using the MS Visual Foxpro Treiber driver... although it defaults back to Other ODBC driver source).

From the tables subdirectory, I choose the import data task. I follow the process of going through the wizard and use the option Copy tables and views from the source database. I choose the table, in the Column mappings, I fix the SQL for the create table (changing char to varchar and smalldatetime to datetime, etc..).

Then when I go to run the package it throws me an error stating:
"Insert error, column 6('eff_date', DBTYPE_DBTIMESTAMP), status 6: Data Overflow. Invalid character value for cast specification."

The problem with this is that I checked both my source and destination to make sure that they were datetime. So I really don't know what my problem is.

Any advice?

Thanks,
Smoo

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-31 : 16:10:49

Valid date times... Not all the time are they going to be read as a valid date time. I get this problem often enough. Create a table with all fields as varchar. Get the dts to load this new table with all columns set to varchar. Now you'll be able to run an isdate() function on col6 to see if they are all valid dates.

Past experiences with this include me recieving dats as the 0th day of march or the 24:60:60 for a time stamp (invalid time... 59 is the max for seconds and minutes). Go trhough and verify your data like this. Worst case scenario, you use dts to create a table with all columns defined as varchar, load it with data, then run a convert statement to convert varchar into a date time and then move this all to a final table with all columns defined properly.

-----------------------
SQL isn't just a hobby, It's an addiction