sqlfriends (5/20/2013)
We would like to do this in SSIS package:1. pull a list of employeeID, with their address from a SQL server table using select query (we can use Execute SQL task)
2. Using above record set, to update the same employees in an oracle database that has a stored procedure that we can pass employeeID, and it will match employeeID to update their address.
Can no2 be done using SSIS? can we call a stored procedure in Oracle?
Thanks,
Sure. Your package would have a Data Flow containing an OLE DB Source and a Recordset Destination (really it is an ADO.NET DataSet under the covers) to capture the employeeID information from SQL Server. Then connected to the Data Flow would be a Foreach Loop Containiner setup to iterate over your DataSet (stored in a Variable of type Object). For each row in your DataSet you will use an Execute SQL Task to execute the Oracle Stored Procedure.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato