locked
Hresult e_fail error in ssis RRS feed

  • Question

  • Does anybody know about this error;

    Error HRESULT E_FAIL has been returned from a call to a COM component. (Microsoft Visual Studio)

    I was trying to use a query in oledb source through "Sql commang from a variable"

    "select * from test where load_tmstp = "+@v1"'

    (@v1 has the value of sysdate, datatype is datetime)

    Thanks

     

    Monday, January 16, 2012 8:11 PM

Answers

  • If I recall right, this is the Oracle's timestamp data type of a variable therefore the following must remedy your SQL:

    TO_DATE('2012-01-16 15:00:00','yyyy-mm-dd HH24:MI:SS')  In your expression it should be: "select * from test where load_tmstp = TO_DATE('" + @v1 +"','yyyy-mm-dd HH24:MI:SS')"


    Arthur My Blog
    • Proposed as answer by Eileen Zhao Wednesday, January 18, 2012 3:08 PM
    • Marked as answer by Eileen Zhao Monday, January 23, 2012 2:24 AM
    Tuesday, January 17, 2012 3:44 AM

All replies

  • Sorry actually the query is

    "select * from test where load_tmstp = "+@v1+""

    Monday, January 16, 2012 8:13 PM
  • Must be that you do not set it properly (like this single quote I can see is odd). What does it get evaluated to?
    Arthur My Blog
    Monday, January 16, 2012 8:30 PM
  • I tried to evaluate:

    It says:

    The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator

     

    so i tried :

    "select * from test where load_tmstp = (DT_WSTR, 10)"+@v1+""

    IT IS STILL NOT EVALUATING

     

    Monday, January 16, 2012 8:54 PM
  • Arthur

    It works now

    "select * from test where load_tmstp = "+(DT_WSTR, 10)@v1+""

    It evaluates as:

    select * from test where load_tmstp = 1/1/2012

    But when i use this in oledb source, it still gives me same error

    Error HRESULT E_FAIL has been returned from a call to a COM component. (Microsoft Visual Studio)

     

    Monday, January 16, 2012 9:01 PM
  • Sorry for the delay,

    did you try this expression against your data storage? I think it is missing single quotes

    select * from test where load_tmstp = '1/1/2012'


    Arthur My Blog
    Monday, January 16, 2012 9:19 PM
  • i tried that too

    error;Error: ORA-01843: not a valid month

    Monday, January 16, 2012 9:25 PM
  • So here we are almost solved it, it now is about the correct PL/SQL.

    Besides, did not know about your database.

    How is it defined this load_tmstp field on the Oracle end?

    I suspect you need to use a conversion function to extract the data this way like

    CAST('1/1/2012' AS TIMESTAMP)

    Arthur My Blog
    Monday, January 16, 2012 9:32 PM
  • the oracle load_tmstp field is of the form:

    11-JAN-12 04.00.12.000000000 PM

    So how should i write the expression (sql sommand from a variable in oledb source)

    I am not sure but is it something like below:

    "select * from test where load_tmstp = "+cast(@v1 as timestamp)+""

     

    Thanks

    Monday, January 16, 2012 9:47 PM
  • If I recall right, this is the Oracle's timestamp data type of a variable therefore the following must remedy your SQL:

    TO_DATE('2012-01-16 15:00:00','yyyy-mm-dd HH24:MI:SS')  In your expression it should be: "select * from test where load_tmstp = TO_DATE('" + @v1 +"','yyyy-mm-dd HH24:MI:SS')"


    Arthur My Blog
    • Proposed as answer by Eileen Zhao Wednesday, January 18, 2012 3:08 PM
    • Marked as answer by Eileen Zhao Monday, January 23, 2012 2:24 AM
    Tuesday, January 17, 2012 3:44 AM