I work with all of them as a Microsoft Certified Trainer, particularly around the Business Intelligence SQL space. I tend to do quite a bit of work around the SSAS space, SSIS, SSRS, and integrating that into a lot of SharePoint and other spaces.
The challenge is building from the KPIs. Being able to put your KPIs in place starting with understanding a board of directors and their needs, and being able to build the KPIs for an organization from that level is important. Then being able to deliver those on a dashboard, such as Power BI, or something else, also matters. Additionally, being able to build cubes that allow for rapid analysis over a long period of time is crucial. The one thing which is missing often enough is building out your data warehouse with your data models, so helping to build the data models is very important. I appreciate how it handles large data in your warehouse.
Stability is rated at 10. One other important aspect I appreciate is that SSAS is included in the base installation of SQL Server. Obviously, it requires installation, but it is readily available, which is a major strength. It's all about setting it up, configuring it, and then using it. If there are additional costs associated with it or separating it as a second product, that would be a disadvantage.
The area of improvement is really in education. Microsoft is trying to push everything as a Power BI solution or trying to get people to solve the problems which are solved with SSAS in another space in Power BI, or in Power Pivot, is not enough. There's not enough marketing, conversation, and support around that space. As a result, we end up with people not understanding that you need to build your models correctly, and then they try to model everything inside of Power BI, or another visualization tool, without first building the data model.
That leads people to consider alternate solutions because SAP and others argue that their whole thing is in memory, and they disseminate misleading information. Additionally, what would be very helpful is local user group developments, so getting people around the table and teaching them how to use it. That is the biggest problem; it's not the technology itself. The challenge lies in Microsoft withdrawing a lot of the qualifications and watering down its emphasis, leading to a perception that this is supposed to be an elite product.
Curious people's experiences when they mention "fail a lot" or scalability. I've used many ETL tools in my career - most of them very expensive and I'd put SSIS up against any of them for reliability and performance (within limits).
Scalability is largely comparing to expectations and it depends on your expectations. I think people too often compare completely different architectures and are surprised when they scale differently. SSIS is single server. No MPP going on here folks. You get a lot more than you've paid for (which is really nothing is you already own SqlServer). SSIS sure beats the open source stuff I've seen out there that really sucks. Try Pentaho written in Java if you want slow. I've read where people have custom coded front ends to fire multiple SSIS servers and there's ways of partitioning data flows but if you are getting into that you might be on the wrong tool. Consider the opposite - most people are running SSIS on the target database box so its competing with the database server as well as not utilizing more than one server. I'm doing that and actually getting quite great performance (again - its all about expectations).
So yes if you need millions per second SSIS is not the tool you want. My benchmark with SSIS is @10,000 rows per second to stage large rows through a data flow. I'm guessing if you need a lot faster than that you have significant volumes and big pockets so why would you look at a free tool that's designed to be installed on a database server?
As for failing, it would only fail due to buffers if you did something with altering buffers that you should not have done. That would be your bad sorry. Or you're doing something else silly like running on 4gb VM and didn't set a max memory on the Sql Server so basically everything crawls or fails. Hey - some of you are laughing but some are probably scratching their heads and asking, so what's wrong with that?