September 14, 2012 at 12:03 am
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.
Thanks,
Roy
September 14, 2012 at 12:55 am
swarnavor-874112 (9/14/2012)
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.
Thanks,
Roy
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 15, 2012 at 5:10 pm
800000 is not a huge number but still larger than, say, 3000-4000 rows in oracle. Please follow the pattern of having a stage table in between your ssis and the actual target table.
So that way,
First, have everything in a stage table.
Then write a stored procedure which will do a mismatch between the stage and the target table and update / insert accordingly. Even there, might i suggest, don't use a cursor to loop through the rows, use the MERGE command.
September 16, 2012 at 11:08 am
I got it - but what is the reason for this stalling of flow of the package, the say the 8 lacs + rowsin the source and lookup table ?
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply