locked
Importing records from Oracle to SQL - Receiving error ORA 00933 SQL Command Not Properly Ended RRS feed

  • Question

  • I am a newbie, so please excuse my deficiency.  I was successful connecting and importing data from Oracle to SQL Server 2008 R2 using SSIS for Oracle 10g, but when I try to add the Update command to my SQL statement I receive "ORA-00933 SQL Command Not Properly Ended Microsoft OLE DB Provider for Oracle.

    Here is what works perfectly;

    SELECT name,contract,awdnumber,sponsorname FROM myoracletable WHERE audit_stamp >= substr(sysdate,1,10) and processed_flg='N'

    However, when I add this additional info to the end of my statement I get the ORA-00933 error;

    UPDATE myoracletable SET processed_flg='Y' WHERE processed_flg='N' and audit_stamp >= substr(sysdate,1,10)

    My goal is to import records into SQL, then UPDATE the newly imported records in ORACLE, by changing the process_flg from N to Y.  Can someone point me in the right direction, am I close?  Any help would be greatly appreciated.

    Tuesday, February 7, 2012 1:08 AM

Answers

  • Hi Larry

    The UPDATE statement is confusing things - the OLE DB source presumably is only expecting the SELECT statement which returns a record set.

    Extract the data in your data flow as before, then using a separate Execute SQL statement on the control flow run your update and you should be fine.


    James Beresford @ www.bimonkey.com & @BI_Monkey
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex

    • Proposed as answer by Koen VerbeeckMVP Tuesday, February 7, 2012 8:29 AM
    • Marked as answer by Larry Frazer Tuesday, February 7, 2012 7:01 PM
    Tuesday, February 7, 2012 2:31 AM

All replies

  • Hi Larry

    The UPDATE statement is confusing things - the OLE DB source presumably is only expecting the SELECT statement which returns a record set.

    Extract the data in your data flow as before, then using a separate Execute SQL statement on the control flow run your update and you should be fine.


    James Beresford @ www.bimonkey.com & @BI_Monkey
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex

    • Proposed as answer by Koen VerbeeckMVP Tuesday, February 7, 2012 8:29 AM
    • Marked as answer by Larry Frazer Tuesday, February 7, 2012 7:01 PM
    Tuesday, February 7, 2012 2:31 AM
  • Hello BI Monkey,

    Thank you very, very much. It worked like a charm and was easy for a newbie to follow.  I now have 2 automated SSIS packages and the knowledge to configure more.

    Regards-

    Tuesday, February 7, 2012 7:05 PM