• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

SSIS Package Hangs

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
nandakrishna
Asked:
nandakrishna
2 Solutions
 
GSGDBACommented:
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
 
radcaesarCommented:
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
 
nandakrishnaAuthor Commented:
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
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.

 
rmm2001Commented:
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
 
nandakrishnaAuthor Commented:
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
 
nandakrishnaAuthor Commented:
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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now