SSIS is the most frustrating, worthwhile bundled app I've seen. It's gotten significantly better but for what is essentially a giant XML writer they sure do like to hide all the options and configurations. I love the API though, so handy.
I don't think so. I used it since it was DTS, not SSIS and whilst Informatica is better at all things ETL, SSIS is great for what it can do out of the box. Besides, if it can't, you can always do it via custom C#or VB extension.
Just finished a multi-terabyte data warehouse project where all data is loaded using SSIS and ControlM and it works great! The dev process was also easy and trouble-free.
Anyways, yes, it's very powerful and an excellent tool but I always have to describe it as awfulsome. SSISDB has made great improvements to the setup and promotion of objects which previously could be a pretty big headache (especially when you're not the one deploying etc). Functions for the various tasks/settings can be hidden and it can take a while to convince newcomers that the options are available, you just have to...poke around a bit. It comes with a substantial amount of controls.
The metadata/conversion of data can be frustrating but automation greater improves this with a quick controlling framework and the sys tables, it also makes moving data across hundreds of tables a breeze. It is very fast and very stable and I'm glad it's getting improvements and having focused development.
I tried Pentaho, but our ETL tasks are being written by engineers (at least right now), and it seemed much clunkier than just writing ruby scripts. Is it worth it in the long run?
If you just need to move data from A 2 B than no. If your transformations are complex and will grow overtime than yes, the time and learning curve are justified IMO.