sturner (11/4/2011)
For shops that have no .net development expertise (or desire to do so) its a good tool. Also very good for shops that have lots of varying but lightweight integration tasks to do on a regular basis.Having said that, for those that have a few specific and consistent integration jobs that are a regular part of the production environment I would always use a purpose built solution in .NET (or C++ if you need that level of performance). In my experience the result is more efficient and reliable solution because you can add robust error detection and recovery procedures that make these jobs bulletproof. Does require competent .net software developer(s) though.
Yep thats effectively what we did on the product I mentioned earlier when we dumped SQL 2000 (and hence DTS) and moved to SQL 2005/8. We wrote a solution into our .NET product rather than attempt to convert everything to SSIS (the conversion tool found lots of things in the DTS package that would require intervention)