none
SSIS Error Code - 0x80040E07 Conversion failed when converting date and/or time from character string RRS feed

  • Question

  • pls how do I rectify this error??

    SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E07.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E07  Description: "Conversion failed when converting date and/or time from character string.".


    Monday, March 11, 2013 9:39 AM

Answers

  • Hi there - it sounds like in your SQL query you are pulling a date which SSIS is seeing as just a character string... -

    Its a good idea to CAST your queried column so that SSIS validator knows explicitly what it is dealing with.

    i.e.

    select cast(columnname as date) as datecolumnname

    Then on your OLEDB source componenet you should click on the MAPPINGS to refresh the metadata.

    Then open the down stream componenets right through to the Destonation and view mappings to ensure this new datatype definition flows down.

    Remember you can open the properties of the arrows and choose METADATA to ensure the columns types and that they are as you expect.


    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training London

    • Marked as answer by Eileen Zhao Monday, March 18, 2013 3:03 AM
    Monday, March 11, 2013 9:45 AM
  • Hi

    This error mainly occurs when it is unable to convert the string to datetime, for the following reasons in the given column

    1. Mainly it might contain some invalid charaters that is stopping while converting to datetime.

    2. Check whether the format is correct or not , i mean if the date format is wrong say mm-dd-yyyy and the format which is incoming of dd-mm-yyyy, then you may get error for some records where date is greater than 12.

    3. Specified limit for date is exceeding. if the date column is smalldate

    Regards

    Naveen

    • Marked as answer by Eileen Zhao Monday, March 18, 2013 3:03 AM
    Tuesday, March 12, 2013 11:24 AM

All replies

  • Hi there - it sounds like in your SQL query you are pulling a date which SSIS is seeing as just a character string... -

    Its a good idea to CAST your queried column so that SSIS validator knows explicitly what it is dealing with.

    i.e.

    select cast(columnname as date) as datecolumnname

    Then on your OLEDB source componenet you should click on the MAPPINGS to refresh the metadata.

    Then open the down stream componenets right through to the Destonation and view mappings to ensure this new datatype definition flows down.

    Remember you can open the properties of the arrows and choose METADATA to ensure the columns types and that they are as you expect.


    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training London

    • Marked as answer by Eileen Zhao Monday, March 18, 2013 3:03 AM
    Monday, March 11, 2013 9:45 AM
  • Hi

    This error mainly occurs when it is unable to convert the string to datetime, for the following reasons in the given column

    1. Mainly it might contain some invalid charaters that is stopping while converting to datetime.

    2. Check whether the format is correct or not , i mean if the date format is wrong say mm-dd-yyyy and the format which is incoming of dd-mm-yyyy, then you may get error for some records where date is greater than 12.

    3. Specified limit for date is exceeding. if the date column is smalldate

    Regards

    Naveen

    • Marked as answer by Eileen Zhao Monday, March 18, 2013 3:03 AM
    Tuesday, March 12, 2013 11:24 AM