none
Invalid character value for cast specification

    Question

  •  

    I have a Data Flow task that is simply copying data from table A to table B. (there is a derived column in between however)

     

    All the columns in table A are varchar(255). The columns in table B vary: floats, decimals, etc

     

    However, I am getting a bazillion errors when I run this, each of them to the effect that:

     

     Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification". An OLE DB record is available.  Source: "Microsoft SQL Native Client" 

     

    I don't understand why this is happening... I've succesfully run similar scenarios with other packages, ie) moving data from one table to another table even though the tables have different data types.

     

    Help

     

    Thanks

     

     

    Wednesday, October 31, 2007 6:36 AM

Answers

  • Ok, nevermind.

     

    The problem is not nulls, and it doesn't matter whether or not the column data type match.

     

    The problem is blanks in the source columns........ this is what is causing the invalid character value for cast specification.

     

    So I guess the solution is to remove the blanks...............

     

    Wednesday, October 31, 2007 3:53 PM
  • That's correct - a blank string can't be converted to a float.

     

    Seems like we had a previous thread going about using a script to check all columns for a value and change them to another value. You might be able to leverage that as a starting point.

     

    Wednesday, October 31, 2007 4:05 PM
    Moderator

All replies

  • Hmm, it appears that the problem is related to the fact that table A has NULL values, but I don't understand why this is a problem.

     

    Can someone please explain why this would be...........? And how to get around it.

     

    thanks

     

    Wednesday, October 31, 2007 6:50 AM
  • Actually, I am not at all sure if the problem is the null's

     

    However, I am running this EXACT scenario in another package, with zero problems - the only difference is that table A in this case has no nulls........ I can't imagine this would be the problem... it seems buggy to me....

     

     

    Wednesday, October 31, 2007 6:57 AM
  • I think the error is quite clear, you know what it means, so now, break it down and work out what the value is that cannot be converted. Use the Error output as a start, it allows you to determine which column is failing. Using a data viewer on the error output is a quick way of finding out the data values. You can then review the data value and the target data type and see what is wrong.

     

     

    http://msdn2.microsoft.com/en-us/library/ms166707.aspx
    Wednesday, October 31, 2007 9:08 AM
    Moderator
  • As I suspected, the problem was NULL values... I know with a flat file you can check "Retain null values from the source as null values in the data flow" in the flat file source editor.

     

    But in this case, I'm moving data from one table to another - is there a similar setting? I can't believe it's not possible to copy NULLs across tables.

     

    Help

     

    Wednesday, October 31, 2007 3:13 PM
  • It is certainly possible to transfer data including NULL values between two tables.

     

    Have you compared the data types for the offending columns?

    Wednesday, October 31, 2007 3:15 PM
    Moderator

  • The problem seems to be in summary, copying data from one column to another where:

     

    1.) the data types are different (in my case varchar to float)

    AND

    2.) there are nulls in the source columns

     

    As I mentioned, this exact copy process works in another package, the difference being the source table doesn't have nulls, whereas this one does

     

    I guess the solutions are:

     

    1.) change datatypes to match

    OR

    2.) remove nulls before doing the copy

    Wednesday, October 31, 2007 3:34 PM
  • Ok, nevermind.

     

    The problem is not nulls, and it doesn't matter whether or not the column data type match.

     

    The problem is blanks in the source columns........ this is what is causing the invalid character value for cast specification.

     

    So I guess the solution is to remove the blanks...............

     

    Wednesday, October 31, 2007 3:53 PM
  • That's correct - a blank string can't be converted to a float.

     

    Seems like we had a previous thread going about using a script to check all columns for a value and change them to another value. You might be able to leverage that as a starting point.

     

    Wednesday, October 31, 2007 4:05 PM
    Moderator
  • Yes, I'm embarrassed to admit I've already asked this question. It was late at night :-)

     

    I guess the point is really driven home now - no blanks with numerics!!

     

    Wednesday, October 31, 2007 4:08 PM
  • Hi friends,

     

    I am getting the same error but in my case both my source and target columns have varchar(20) as a datatype. moreover its happening only for update. Insert is fine. and then when i remove the target column from the ole db task(i.e. remove the column from the update query) it doesnt give any error. can you pls help?

    Thursday, November 22, 2007 2:16 PM
  • Need a bit more information. Have you used the error output to determine which rows have the problem? Is there anything unique about the data in those rows?

     

    Also, it's helpful if you give a precise description of what tasks and components you are using. From your description above, I'm not positive if you are using an OLE DB Destination, OLE DB Command, or an Execute SQL Task.

     

    Thursday, November 22, 2007 2:44 PM
    Moderator
  • change ERROR OUTPUT within your dataflow task to IGNORE FAILURE on the offending field within your source.  do the same to your destination then you'll be good to go bro...ignore those worthless answers above...l8

    • Proposed as answer by SQL User 2005 Wednesday, July 10, 2013 10:06 PM
    • Unproposed as answer by SQL User 2005 Wednesday, July 10, 2013 10:26 PM
    Tuesday, January 22, 2008 11:25 PM
  • Another way to get this same error is to have a variable hold the value of a GUID that you wish to use as a parm in a proc. If you don't put the curly brackets around a GUID value in the value column of the variables, you get this same not-helpful error message: 
    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E21  Description: "Invalid character value for cast specification".
    Always learning how much I do not know.
    Wednesday, November 25, 2009 5:01 AM
  • I just had this problem when reading an Access 2003 table into a pivot in Excel 2003. Excel/MS Query doesn't like the field formatting my Make Table query decides is correct for the field. (The field is revenue, i.e. either numerical with 0-2dp or Null. Access decided on Decimal, whereas I would have chosen Double.)

    I did a bit of testing after reading the above comments.

    An Update query to change all Null fields to 0 (zero) didn't make any difference.

    However, changing all Null fields to Null (sic!) worked perfectly. I now use the following workaround in my Make Table query:

    Revenue: IIf([Data Table].[Revenue]=0,Null,[Data Table].[Revenue])

    This has the bonus side effect of Access creating the field in the new table as Double instead of Decimal.

    Wednesday, May 19, 2010 1:18 PM
  • I had the same problem and it was because of the difference in data length in one record in the source table.  the length was 4 in the source table and the maximum length is 3 in the destination table.

     

     

     

     

    Wednesday, August 04, 2010 8:49 AM
  • Removing blanks in source solved this for me as well, even though the blanks were an integer data type.

    Thanks!

    -Nathan

    Thursday, March 01, 2018 6:38 PM