sql server data import error

Posted on 2011-10-26
Last Modified: 2012-06-27
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.
Question by:sxxgupta
    LVL 142

    Accepted Solution

    - Copying to [dbo].[Service Database] (Error)
    	* 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:

    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.

    Author Comment

    ok, will do.  What doe (511) and (386) mean in the parentheses?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I have no idea

    Author Comment

    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.


    Author Comment

    there was some bad data in the date field.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now