I am trying to execute a query against oracle with a where clause including
BETWEEN date AND date2
The first round of research shows that oracle's OLEDB provider does not allow parameterized queries, and they must be set in variables. So I created 3 variables. 2, to save dates that are filled correctly, and one to store a query with an expression ending in:
BETWEEN to_date('" + (DT_WSTR, 30)@[User::lastRun] + "','DD/MM/YYYY HH:MI:SS AM') AND to_date('" + (DT_WSTR, 30) @[User::thisRun] + "','DD/MM/YYYY HH:MI:SS AM')"
When I click "Calculate Expression" in the expression builder, it evaluates correctly
BETWEEN to_date('1/1/1900','DD/MM/YYYY HH:MI:SS AM') AND
to_date('1/1/2010','DD/MM/YYYY HH:MI:SS AM')
And I can successfully run this against oracle using other tools.
But when I try to run the package, I get:
[mySource 1] : SSIS DTS_E_OLEDBERROR. OLE DB . : 0x80040E51. OLE DB . : "OraOLEDB" Hresult: 0x80040E51 : " , SetParameterInfo .".
mySource - OLE DB. AccessMode, "SQL Command From Variable", SQLCommandVariable - .
, , DATE :
to_char(PT.CREATED_DTTM,'DD/MM/YYYY HH:MI:SS AM')
SSIS, , . - , ?
. , "from variable" , , , , .
