Bulk Insert from a text file (from Access 2000) to SQL 2000

[Nickela]

I exported a table from an Access 2000 Database into a text file that I am using to import into a table in SQL 2000 through DTS. I have created a format file that defines the columns in the text file and maps them to the appropriate columns in the SQL table. When I run the DTS Package, I receive an error that says:

Bulk insert data conversion error (type mismatch) for row 2, column 7 (StartDate)
Bulk insert data conversion error (type mismatch) for row 1, column 7 (StartDate)

The StartDate field in the Access 2000 Database was defined as Date/Time and is defined as SmallDateTime in the SQL 2000 table

I have tried defining the StartDate field in the format file as SQLDateTim4 and SQLDateTime and receive the same error. Any thoughts on how to fix this?

Thanks for your help,
Nickela

 

[smin]

Try changing smalldatetime to Datetime in sql2000.

 

[Nickela]

Thanks for the response smin,

I modified the column in the table in SQL 2000 from smalldatetime to datetime and am still receiving the same error.

In the text file that I created the StartDate field has a first entry of:

1/4/2001 0:00:00

Any thoughts on anything else I could try and modify?

Thanks for your help.

Nickela

 

[smin]

Here are some things you can try.

Try SQLCHAR instead of SQLDATETIME in your format file.
Have DTS to generate format file for you.
Have Access to export the date column in '01/04/2001 00:00:00' format. SQL server might like that better.
Import the date column in varchar form and do something about it later.
Or direct import/export from Sql Server/Access and at the same time export to flat file.

etc..

 

[cehepker]

Hi,

I was wondering if it would not be easier to just use the DTS to import directly from the Access table.

Carla