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

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.

Answer 1

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

Answer 2

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-