I'm working on an Integration Services package in SQL Server 2005 and I have created a destination table in a SQL Server 2005 database. DateColumn1 in this table has a datatype in SQL Server of datetime. The source uses a date with a DT_Date data type in Integration Services. When I execute the package I get the following error:
Validation error. TransformTask: SQL Server Destination : The column "DateColumn1" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.
I can right click on the destination table and select Advanced Editor to try to change the External Columns datatype from "database timestamp [DT_DBTIMESTAMP]" to something like DT_DATE or DT_DATETIME, but it changes itself back to timestamp.
I get this same error with other packages every time I try to drop date data into SQL Server 2005 from Integration Services. Anyone know a solution? Thanks!