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: Tuesday, September 9, 2014 8:16 AM
Points: 2, Visits: 63
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Monday, July 28, 2014 12:04 PM
Points: 35, Visits: 98
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: Tuesday, September 9, 2014 8:16 AM
Points: 2, Visits: 63
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