Solved

SSIS Package Hangs

Posted on 2010-11-26
7
868 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

726 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