I am using SSIS 2012, to import an oracle table to Oracle, i am working with an 8,00,000+ lacs rows source and destination, the rows already in the destination will be updated using a OLEDB Command object and new rows inserted. This works when I am limiting the source dataset to 5000, but when the source dataset is 10,000 or more the package hangs(keeps executing for ever after). I have used a Lookup on the destination table to find old and new rows based on a key column. I have tried full cache and partial cache in the lookup but no difference.
Please help solve this problem.
8,00,000 is not a number I am familiar with.
To improve performance, I suggest that you change things a little, as follows:
1) Instead of using the OLEDB Command for updates, send all of the rows to be updated to an empty staging table.
2) At the end of the import, run a stored proc on Oracle to process the updates in one go.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.