• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

sql server data import error

I am new to importing data using sql server 2008 management studio.  I am trying to import data from an Access table and get an error.  I have attached the log error file.  Not sure how to fix this.
Report1.txt
0
sxxgupta
Asked:
sxxgupta
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
- Copying to [dbo].[Service Database] (Error)
	Messages
	* Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
	An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".

Open in new window


the error is "quite clear", meaning that (at least) 1 record/column has data from the input that does not match the destination's field (date), at least not using implicit conversion.

please make sure that the source table has the data in the format needed, you might want to read this article for considerations about date/time:
http://www.experts-exchange.com/A_1499.html


others like
* Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date" (511) on input "Destination Input" (386). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Open in new window

could mean that the source data has 100 characters, and the destination table has only a varchar(20), or the destination is int, but the source has data that is bigger than what "int" data type can support.

you need to check the specs, source, destination and eventually the transformation rules of the data package.
0
 
sxxguptaAuthor Commented:
ok, will do.  What doe (511) and (386) mean in the parentheses?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have no idea
0
 
sxxguptaAuthor Commented:
Ok, how do I change my date format.  The Access table has a field called "Date" and I need to convert this field also in my SQL table to be datetime.  I was successfully able to migrate the data as varchar(50).  But not need to change that data to datetime.  I tried the alter table alter column method but got an error:

ALTER TABLE [test].[dbo].[Service Database]
ALTER COLUMN Date datetime                                   --The field here is also called Date and the error was:

Msg 242, Level 16, State 3, Line 17
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

0
 
sxxguptaAuthor Commented:
there was some bad data in the date field.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now