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)
