Solved

SSIS Package Hangs

Posted on 2010-11-26
7
865 Views
Last Modified: 2013-11-10
I am using SQL Server 2005 with SP2, When I execute a single SSIS package to extract a large data(around 4 Million), the package hangs as soon as it reaches 550 Mb. The package is running on Windows Server 2003 with over 6GB of RAM. Can someone help me with how to increase these memory options so that it can take Maximum amount of memory available (i think its 2GB & 3GB if we set it in boot.ini) or atleast increast it by 1 GB.

I cannot make any changes to the package as it runs fine with low volumes of data.    
0
Comment
Question by:nandakrishna
7 Comments
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34219354
HI,
Could you please try using BCP.
It is much faster for bulk operations.

See the below link for more details on BCP.
http://msdn.microsoft.com/en-us/library/ms162802.aspx
0
 
LVL 9

Accepted Solution

by:
radcaesar earned 250 total points
ID: 34220079
What is the access mode used in the Destination? "Table or view" or "Table or view fast load"?

Try to use the first one.....

Is it runs fine in BIDS?

Also, what is the CACHE mode you are using?

Try using FULL Cache......... But it will consume more memory.
0
 
LVL 3

Author Comment

by:nandakrishna
ID: 34221661
The access mode used is Table or View, the issue occurs when i run it through BIDS and through the dtexec utility.

We are using 6 look ups in the package. My question still holds, why does it hang when it reaches 550 MB ?

is there a way to configure this setting to execute the package with a manual parameter that can use Max Memory size of say XXX MB
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 7

Assisted Solution

by:rmm2001
rmm2001 earned 250 total points
ID: 34223575
Change your access mode to "Table or View - Fast Load" and try running it out of the command line:

C:\Program Files\Microsoft SQL Server\90\DTS\binn\dtexec.exe -f "Path to your package"
0
 
LVL 3

Author Comment

by:nandakrishna
ID: 34299009
Sorry for replying late. We are using a 3rd party component for Bulk Load Destination. I can say the issue does not occur because of the component as we use much bigger data(around 1000x compared to the current package) loads using the same component.

I am looking at setting some memory options that can be set while running the package and the package hanging at around 550 Mb memory looks odd to me.
0
 
LVL 3

Author Closing Comment

by:nandakrishna
ID: 34533911
The suggestions by experts did not help much with the solution. Its more of an unanswered question.

Thanks again for the help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now