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.

Relability? [part 2, 0xC0202093 error]

In my previous thread, we established that my system does not have SP1 applied. Apparently packaging of SQL Server SP1 is not so easy and while SQL Engine was updated SSIS is still in RTM.

Anyway, after months(!) of running we hit following:

An error occurred while initializing the flat file parser.
The PrimeOutput method on component "Flat File Source" (27679) returned error code 0xC0202093. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038.
Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Thread "WorkThread0" has exited with error code 0xC0047039.

I could not find anything regarding 0xC0202093, but re-starting SQL & IS services cured the problem. I am putting it here, so others learn from our experience. Probably re-starting IS engine would be enough.

Someone may say that re-starting of the service is a piece of cake. In the developers enviroment - it is. In the enterprise production system, where corporate rules are applied - it is a nightmare: it has to be approved, it has to be documented ("Can you prove that re-starting will cure the problem?") and it has to be done by a DBA - and that together cost real money.

Googling 0xC0202093 comes up with hits, (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0202093.html), and the base error message is "An error occurred while initializing the flat file parser." So it relates to the Flat File source you are using, but above that no more infomation is documented, so not entirely helpfull.

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

|||

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

Thanks. It is not related to the size (the one of the files has 200 2-column rows) and all packages using flat file connection were affected. Unfortunatelly, I cannot reproduce the problem. Another thing was that files were on the server files. The same packages, reading from the local driver were fine. [We use dynamic connections where file location is read from the database]. So it might be that flat file parser had problems with networking - don't know.

sql