Import of Data from Access DB

Hello,

I'm trying to import some data from a table in an Access Database into my SQL Server.

It's one field I'm having a problem with and I don't know how to get round it.

Basically the field in Access is called "DueToStart" and is a "Date/Time" type field.
It shows values as "09:00:00" etc.

When I try to import this table into SQL, I get the following message, and I don't know how to get round it.

"Error as destination for row number 1. Errors encountered so far in this task: 1.
Insert Error, column 3('DueToStart', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification."

Does the access column contain only time?

|||Yes is does|||Is there a target table in the SQL Server or is it created automatically when you import the table? The Import Export Wizard in SQL Server automatically creates a table when the table does not exist when importing. Either the table destination exists or is created by the import wizard, check that the definition of the column in the SQL Server is datatime and not smalldatetime.|||

It seems that the column contains only time information and no date imformation. As SQL Server knows only combined data types (e. g. DT_DBTIMESTAMP), I wonder what date it should insert. Maybe 1/1/1753 but maybe that is the problem.

What about a viewer for the output of the data source?

|||Alas_gr - The table already exists in the new SQL DB. The format is smalldatetime and this is what it needs to be.

Anonymous - What do you mean by "viewer for the output data source" ? Sorry but I'm brand new to this and trying to find my way blindly !
|||

I think the problem occurs because the value in your Access table that is either too small or too large for the SQL data type. So try to switch it to datetime, and do the import, and see if it works.

As for the viewer, it can be useful only if you are using Visual Studio BI to design a package. If so, in the data flow designer, if you right click on the connector between the source (access) and the destination (sql server) there is a choice (Data Viewers). So next time you run the package you can see what data you are importing.

|||

This thread deals with an similar issue. In short the date time column from access had to be placed in a SSIS string variable and then casted to date/time.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2071251&SiteID=1