Answered by:
Hresult e_fail error in ssis

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