I am creating a table in Oracle using the execute sql task of ssis..the code is as follows:
BEGIN
execute immediate '
create table mem1 nologging as
select distinct t1.*,
CAST(TO_CHAR(ADD_MONTHS(TO_DATE(sysdate, ''MM/DD/YYYY''), -3), ''yyyymm'') AS NUMBER) as Report_mth
from pat_1mon t1,
pat_6mon t2
where t1.pat_uid = t2.pat_uid (+)
and t2.pat_uid is null
group by t1.pat_uid';
END;
I am getting the following error when the package is executed:
[Execute SQL Task] Error: Executing the query "BEGIN
execute immediate '
create table mem1 nolog..." failed with the following error: "ORA-01843: not a valid month
ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I run the same query in Oracle without 'execute immediate' part, then I am getting the correct result.
Can someone please help me out here..