sqlfriends (5/28/2013)
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.
Not sure I understand the first part, why I need data flow task, shall I just use a Execute SQL task with select statements?
Sure, that's another option. If you set the Execute SQL Task to return a full resultset and map the resultset to a variable of type object you'll get the same result as what I mentioned.
Could you give a step by step guide, thanks much,
should I use oracle udpate, or use Oracle providor for OLEDB or use Microsoft OLE DB provider for Oracle?
THanks
I am not on top of the best options for working with Oracle. I have heard that the Attunity drivers are good though and I have used their offering for Teradata and have been very pleased.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato