Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2012 package hanging without any message Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 4:44 PM
Points: 2, Visits: 62
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
Post #1359052
Posted Friday, September 14, 2012 12:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1359068
Posted Saturday, September 15, 2012 5:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 35, Visits: 97
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.
Post #1359856
Posted Sunday, September 16, 2012 11:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 4:44 PM
Points: 2, Visits: 62
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.
Post #1359902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse