November 18, 2010 at 7:27 am
Hi,
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
November 18, 2010 at 11:43 am
I guess that when you read your data from the SQL Server table, that you have some sort of primary key.
Use a multicast in your data flow and send all the primary keys to a staging/temp table.
After the dataflow, use an Execute SQL task connected with the dataflow with an On Success precedence constraint (meaning that the Execute SQL Task can only run if the dataflow has run succesfully). In the SQL Task, update your source table using the primary keys.
To be almost perfect: place the dataflow and the SQL Task inside a sequence container and set the TransactionOption to Required. This way, if the SQL Task fails, both will be rolled back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2010 at 1:28 pm
thanks for the hint, I'll try it at once!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply