SSIS 2012 package hanging without any message

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS 2012 package hanging without any message...

SSIS 2012 package hanging without any message

Rate Topic

Display Mode

Topic Options

Author

Message

swarnavor-874112

Posted Friday, September 14, 2012 12:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 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

Phil Parkin

Posted Friday, September 14, 2012 12:55 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665

swarnavor-874112 (9/14/2012)

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1359068

n79799

Posted Saturday, September 15, 2012 5:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 35, Visits: 84

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

swarnavor-874112

Posted Sunday, September 16, 2012 11:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 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 »

Permissions