Oracle parameterized queries to update Oracle table do not work

Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.

Anyone knows how to update an Oracle table through SSIS?

Thanks!

Wenbiao

Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.|||

Phil Brammer wrote:

Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.

Thanks Phil,

Your post is really helpful, however, I have a couple of more questions.

1. You said load the updates to a table, this has to be a temp table I guess?

2. Could you please write in more details on how to "perform a batch update" in an Execute SQL task, such as what the SQL statement will look like? Do I still need to provide parameters, such as "update dest_table set xxx=? where yyy=?"

Thanks a lot!

Wenbiao

|||Yes, it is a temp table. But not a #temp table. It's just a physical table that you store the updates in. When you're done, you can issue an Execute SQL task to truncate that table.

As far as the batch update goes: (Something like this, I believe)

UPDATE myTable a INNER JOIN stagingTable b ON a.key = b.key
SET a.field1 = b.field1, a.field2 = b.field2|||

here is an example of the update syntax:

UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

|||Thanks Phil and Rafael, I will give it a try and report back.