Integration Services forcing conversion from datetime to timestamp

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 [699]: 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!
Patrick_McDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pai_prasadCommented:
can u post the scripts for Create table
for both source and destn tables..

0
Patrick_McDAuthor Commented:
Yes, they both contain fields with datetime columns:

--Source Table (in SQL Server 2005):
USE [SpiralIS]
GO
/****** Object:  Table [SDRT-Spiral].[SDRTImport]    Script Date: 03/24/2006 13:13:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SDRT-Spiral].[SDRTImport](
      [DateImportColumn1] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--Destination Table (in SQL Server 2005):

USE [SpiralIS]
GO
/****** Object:  Table [SDRT-Spiral].[SDRTInitialScrub]    Script Date: 03/24/2006 13:10:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SDRT-Spiral].[SDRTInitialScrub](
      [DateColumn1] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
0
pai_prasadCommented:
i didnt get any problems while xfering data from SDRTImport to SDRTInitialScrub
using SSIS !
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Patrick_McDAuthor Commented:
Everytime I create a table in SQL Server 2005 with a column with a datetime data type, and look at it in SSIS under the Advanced Editor, it shows up as a datetime data type.  I've created several new tables and they all do that.
0
Scott PletcherSenior DBACommented:
>> Everytime I create a table in SQL Server 2005 with a column with a datetime data type, and look at it in SSIS under the Advanced Editor, it shows up as a datetime data type.  I've created several new tables and they all do that. <<

That's what it should do, isn't it?


As to the original problem, SQL obviously "thinks" the destination column is a TIMESTAMP instead of a DATETIME.  Just to be sure, you're not using replication in any way, are you?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick_McDAuthor Commented:
Sorry, I wrote my comment wrong.  I meant to say that it shows up as a timestamp.

I decided to try another approach.  I changed both the source and destination in SSIS to timestamp, while leaving the actual SQL table as a datetime.  When I ran the package this time, I didn't get any errors and the data in the table was in fact a datetime.  So, it's weird, but this workaround seems to function.

Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.