When this happens, the only solution I have found is to reboot the machine, as I am unable to even kill the process using the Task Manager. (I am adding timeouts to all of the Execute Process tasks on the version I am developing now to see if that will at least allow them to fail gracefully.) What is surprising is that there does not appear to be an option at the package level OR within the SQL Server Agent to set an overall timeout after which the package fails.īIDS, on the other hand, frequently gets stuck and stops responding when I attempt to save and/or run a package in the debugger. The packages do not fail they just get stuck somewhere during execution and never complete to report either success or failure.
The packages usually seem to get stuck in one of a few different Execute Process tasks that ZIP files (we've been using 7zip but are now testing WinZip to see if it works better for us) or, more rarely, SFTP using WinSCP. With SSIS, we have a couple packages that work fine in the BI debugger (when BI itself doesn't stop responding), but tend to freeze at different stages when running as a task under scheduled tasks in the agent in production. The biggest issue we have is that both SSIS and BIDS have been unreliable.
While I love the improvement in the concept of SSIS over what was available with DTS, the actual implementation has been very unsatisfactory for us - to the point that our director is researching third-party alternatives.
I'm not sure if this is the right place to post this, but since I don't see a dedicated forum under SQL Server 2008 for either SSIS or BIDS, I'm posting this under General.