I need to extract rows from a MSSQL 2008 table and insert them into an Oracle database (SAP). I tried using Linked Server, but each transactions took between 4-16 seconds which was, of course, way too much. So I'm creating a SSIS.
I retrieve rows using a OLE DB Source on SQL Server and mapped the colums on a OLE DB Destination on Oracle (using Microsoft OLE DB Provider for Oracle). So far, it's fine. But I need to update an insert status in SQL, either fail or success. How can I do that?
Each rows inserted successfully in Oracle need to have its status updated in SQL. SSIS will be schedule to run at every 2 minutes, so new rows will be added in the SQL table between time recordset is retreive by the OLE DB Source query and the insertion in Oracle.
Anyone can help me please?
thanks for your time and help