Error inserting TIME using DTS

Posted on 2003-03-02
Medium Priority
Last Modified: 2013-11-30
I am reading the time from a file where the time is in the format HH.mm.ss (the time is 24 hour time eg 17.45.20) and inserting the time into a datetime field in a SQL server 2000 table.  I am using DTS to take the date from the file (delimeted with |) and insert into the table.

If I right click onthe transform (under the transformations tab) and click "test" the task is executed successfully and I can see the time correctly inserted.  However, if I execute the package, The task fails on the time columns with an error of:  

status 6: Data overflow.
Invalid character value for cast specification.

I do not understand why this is occuring, can anyone please help.
Question by:paul73
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 8055775
This is one of the reasons why I always use a staging table to load data using DTS. The field to load the time would be char(8), and it's only after the file loaded to this staging table where I update the field with an explicit CONVERT() statement, or I update another field to get the converted value.


Expert Comment

ID: 9276209
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 10994207
No comment has been added to this question in more than 245 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: angelIII http:#8055775

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question