SSIS Package is "hanging" after validation

My SSIS package will just hang (do nothing) after validation of the package tasks. I realised that it does 2 validation. It then hits "starting exectuing" and then nothing. I mean nothing. It just stays the same. When I look into the logg file, the same message as in the output window. My package has parallel extract of data from the same datasource, but different tables. I dont know if that the problem but i really doubt it because i have done parallel table downloads countless times in version 2000. When i go into the data task window, the source task does no even indicate that its downloading (color yellow). Is there any reason why this will happen Ooo, but the tasks executes just fine when i execute them individually (right click > execute).

Answer this question

SSIS Package is "hanging" after validation

JohnnieK

It is not the SQL team. The OS only provides 2GB of virtual address space to any individual process. A 32 bit number can only reference 4GB of address space and the OS reserves 2GB for system virtual address space and 2GB for application virtual address space (1 and 3 when using the /3GB switch, respectively). All applications are subject to this not just SSIS.

Matt
Steve Schefer
Matt David, could you please elaborate more on the 2gb. When im downloading a table, i suppose the data is held in memory till its all downloaded and then inserted. So if the entire data that im downloading is more the 2gb, you are saying its not possible. Or what you mean is that each field like int, string can hold a maximum of 2gb of data.
doonm

my odbc driver (DB/C 4) is a 32bit driver and that would mean if i should install SSIS in 64x, i wont be able to see my 32x odbc driver from SSIS because it will be registered in the data source manager for 32x under WOW or is there a way to go around this. Would you suggest that im better off with the 32x version of windows server 2003 even though i wont be using the full potential of the 64x machine (which i would like to ).

by the way, i installed the 32 version of SSIS
kmbarz

No the entire virtual memory address space is 2GB. That means everything, the application, the data, etc. SSIS tries to alleviate this by swapping to disk when in low memory condition but sometimes there is not enough swapable so it can still fail.

Also, we don't hold everything in memory. We hold buffers in memory and when they get to a destination they are inserted and then the memory is freed. However, depending on what you are doing in your dataflow we may not be able to swap/free enough to not fail due to out of memory conditions.

You should also note that virtual memory does need some physical memory backing so there are cases when you can not run out of virtual memory but will run out of the physical backing and this can also cause out of memory failures. You don't mention how much physical memory your PC has but generally this is not an issue on 64 bit boxes.

Matt
Ian Fisher
Is there a work around to this problem so i have consume mor virtual addresss space the 2GB "if" that is the problem which i think might be because smaller tables are able to download without any problem.
morixo
what were you doing at the time of the error Idid not do anything however I finished work from internet explorer6.0 and I closed it befor I turn off computer when I closed it the error is appear always
Lorelei

Perhaps there is some blocking on the source system There could be in any number of reasons and I would hazard a guess that the problem isn't in SSIS.

-Jamie
Glowball

You can run the package using the 64 bit version of DTExec if you have installed the 64 bit version of SSIS. That is not limited by the 2GB virtual address space.

Matt
Erbil Yilmaz

Well, it looks from the copied text that you were running this package from the designer (BIDS). If that is the case then you are running in the WoW as a 32 bit process so you only would have a 2GB virtual address space.

Matt
Pinky98

So basically, im screwed. Aaaaaaaaaaaaaaaa, i convienced my manager to get a 64x machine because it will make our process faster and because i was having the same memory problem on the development machine. He is going to cut my pay check into half unless Bill calls him and beg for me. My worry is if 2gb is an issue, it could be that 3gb will be an issue to since i cant really weigh how my virtual memory the processs will need. Im really in a pinch now.

why will the SQL team put a virtual memory limit on the 32x
M.Davoodi

By source system I mean the "thing" from which you are extracting data.

What is the ACT source task There is no such thing as a "source task" so I am assuming you mean "source component".

Is this a custom component (i.e. It is something that isn't provided out of the box with SSIS)

-Jamie
Mbansal

the act source task is an source component but not a custom source component. In any case, i changed the maxconcurrentexecution = -1 to 13. It seemed to start just fine but then failed at the very end when after pulling down almost all of the records. Now, this time the error was

[ACT SOURCE [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutp(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuf

and the warnings i got before that is

[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

Now, I dont know how this is possible but im running on a 64x xeon DP 3.02 with multi processor with nothing running on it except for SQL 2005 at present. It didnt look like it was even using 10% of the machines capabilities so i dont understand why it will complain about memory.
John Szurek

There is no way to do this if your driver is 32 bits. You would have to run the package in 32 bits and then have the limitation of the 2GB virtual address space (you could use the /3GB switch to get 3GB which can sometimes help, but I believe you would actually need to be running a 32 bit OS to use this switch, although I am not certain of this). Since you are running in the WoW you are running the package as if it was 32 bits so using a 32 bit OS wouldn't buy you anything (except as I mentioned perhaps the option of using the /3GB switch).

Matt
WineMan
what do you mean by the source system. I keep getting errors like the one below. Im pulling down a table called ACT with ACT source task and destination task. For some reason, after pulling down a couple of records, it generates this error or sometimes it just hangs.

[ACT SOURCE [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)
SSIS Package is "hanging" after validation

Answer this question