Convert date from access to SQL Server with SSIS

I want to convert a database from access to SQL Server using SSIS. I cannot convert the date/time columns of the access db. SSIS says something like:

conversion between DT_Date and DT_DBTIMESTAMP is not supported.

(Its translat开发者_运维技巧ed from my German version, might be different in English version). In Access I have Date/Time column, in SQL Server I have datetime. In the dataflow chart of the SSIS I have a OLE DB source for the access db, an sql server target and a data conversion. In the data conversion I convert the columns to date[DT_DATE]. They are connected like this:

AccessDB -> conversion -> SQL DB

What am I doing wrong? How can I convert the Access date columns to SQL Server date columns?

Looks like you'll need to add a specific conversion to convert this column to the correct type. It's the DT_DBTIMESTAMP that you need to convert to, not DT_DATE. Using a Derived Column Transformation for instance, you could use an expression like:

(DT_DBTIMESTAMP)YourDateColumn

You use another Provider on the source side. I used the JET provider. I am doing this on a computer where no Access is installed. When I try to create a data connection using the Office 12.0 Access Database Engine OLE DB Provider I get an error message that the workgroup information file is missing. I get this message on the connection test.

The question is if the conversion depends on the db driver you use to get the data. I cannot install Access on the server I am working, so I cannot try it. Could this be the cause of the problem?

Casting D_Date column to DT_DBTIMESTAMP solve the same problem for me.