SSIS Advantage timestamp to SQL Datetime

I am new to using SSIS 2008 and am currently working on migrating data from an old Advantage database over to SQL Server 2005. I set up the SSIS package with a dataflow object with a source and a destination. Copying most of the columns works great, but when I try to copy the Advantage Timestamp columns over to a DateTime field, I get many errors. I believe they are all overflow errors, which is understandable in how Advantage stores the timestamp information. My question is what is the best way开发者_如何学运维 to convert this column into the the correct DATETIME field in SQL Server 2005?

Doing a little thinking I felt I could copy the Advantage TimeStamp column into a varchar column and then run a sql script that would do the conversion for me. I was wondering if there is a more elegant way, or what a normal solution to this type of problem is.

Thank you for all of your help and suggestions!

In your Data Flow you can use a Derived Column transformation you can try casting the Advantage Timestamp to a SQL Server DATETIME (DT_DBTIMESTAMP) data type (note that this is not the SQL Server TIMESTAMP data type.) In the Derived Column transformation add a new column and enter the following expression to convert the data type.

(DT_DBTIMESTAMP) [MyDate]

If this fails, then you can do the type cast to a string, (DT_STR, <>, <>), then manipulate the string using string functions and then type cast to the DT_DBTIMESTAMP in the single expression.

(DT_DBTIMESTAMP) (...string functions...(DT_STR, 20, 1252)[MyDate])

Timestamp is a datatype and has nothing to do with Date or Time data.