?
Solved

SSIS Package Hangs

Posted on 2010-11-26
7
Medium Priority
?
872 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 750 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
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.

 
LVL 7

Assisted Solution

by:rmm2001
rmm2001 earned 750 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

801 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