Relability? [part3, zombie, sorting(?)]

Hello again,
It seems that we are pushing IS to the limits :). And again - still SSIS engine version RTM, so my comment may have been addressed in SP1 or HF1. A package is executed by SQL Agent once every day. Once in a fortnight it stalls - goes to the zombie mode. The status of the package in the SQL Agent monitor is "Executing", while log file reports nothing.

Now, I believe it is caused by sorting component in the data flow task. I have three reasons for doing so: the log file shows that data sources were read; I think I have seen it once in MS Studio and finally - previous version of the package without sorting worked fine for several months.

In our case, we used sorting for merging datasets - we decided to use staging (temporary) tables and lookups to the same task. Going live today, so hopefully it will be a valid work around.

The NSort sort transform is far superior to the stock sort in performance, and in forgiveness when the stock sort would start spooling to disk in lower memory conditions. NSort is not free, and it works well.

Extrasort ( a third party sort transform) also works better than the stock sort with in terms of speed and low memory, but it doesn't work with 64 bit, so it may not be an option for your production environment.|||Try increasing the EngineThreads property of the data flow. There is a known bug that may cause pipeline with async components (like Sort) to deadlock if the number of threads is low.