We help IT Professionals succeed at work.

Integration Services forcing conversion from datetime to timestamp

Patrick_McD
Patrick_McD asked
on
Medium Priority
1,284 Views
Last Modified: 2012-06-27
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!
Comment
Watch Question

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

Author

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
i didnt get any problems while xfering data from SDRTImport to SDRTInitialScrub
using SSIS !

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
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. <<

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?

Author

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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.