Greetings, We have had a few issues lately with the EXECUTE PROCESS task in SSIS 2008 R2. We found that whenever we attempted to execute a process (GZip) over a UNC address via a job it would process indefinately. Now we found a few solutions to this (most notably on this very forum) but nothing seemed to work. Until today I ran the job and it worked a treat. Despite the annoyance factor of having had an issue fixed without knowing the correct solution this raised the spectre of it reoccuring when we created our new live environment. I have been tasked with compiling a document detailing the pro's and con's of using the EXECUTE PROCESS task and what alternatives could be provided. I'm particularly interested in best practice approaches. My initial thoughts are as follows: Pro's: Allows the use of free third party applications and tools like GZip Evades the need to write script tasks or custom components Less development time as the applications are already written Con's: When run over UNC there is a known issue with Windows security permissions (most common fix uses IE settings) Unsupported Can be blocked if multiple packages attempt to run the same process at the same time Applications cannot be customised nor can they be debugged. Apologies for the rather long winded question and the open ended nature of it, but hopefully it's quite a useful one for the boards as well as my team. Regards, Tom

Hi Tom, I would add to your list of cons, the increased Administrative support required at the Windows level, and also the fact that to make the solution work, you need to open up some quite large security holes in the OS. Also, I would comment that you have listed the fact that you do not need a custom component as a Pro. From a time-to-market perspective, this is accurate, but if you had a custom component, then atleast it would be supported, and also, you could code it in such as way as to mitigfate the security risk. Another option would be to use an alternative product, for example if you happen to be pulling files from a vendors FTP site and unzipping them, before processing them, then a product such as Biztalk may be an option, although this would introduce increased cost in licenses and dev time, etc, even if you have the skill sets within your team to support it. Let us know how you get on :)Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Hi Tom, I would like to add that a reasonable alternative is purchasing of third-party product like CozyRoc SSIS+ , which includes much of the extra functionality you need in SSIS solutions and more. The third-party library is supported, completely integrated and doesn't have the issues you experience with Execute Process Task. Many of the issues with the Execute Process Task can be traced back to the fact that the SQL Job Agent uses lightweight user account (the complete Windows user profile is not available) when running scheduled packages. The time you will save just trying to resolve deployment issues and find workarounds is worth the cost of the extra software in my mind.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

CozyRoc, Does your product support files that have been compressed on a unix based system? This is a common reason for using GZip.Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

CozyRoc, Does your product support files that have been compressed on a unix based system? This is a common reason for using GZip. Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/ Hi Peter, Yes, it does support Zip, GZip, BZip2, Unix, Tar compression and decompression.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/