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])