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?
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