Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS Package Hangs

Posted on 2010-11-26
7
Medium Priority
?
875 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

636 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