Its easy to use, fast to develop. Quite easy to also add C# or other code into ETL flow. Transfer to production is much faster than for example with IBM Infosphere Datastage, you can just copy the packages. Haven't found bugs much at all. Its license cost is also quite cheap compared to IBM and Informatica offerings. Overall when choosing a new ETL software I would look into SSIS or one of the open source tools.
-If you want to connect to SAP for example you have to buy add-in from other company and the same applies to many other sources.
-It doesn't have a built-in version control, so you have to get that from other vendors too (so it lacks support for multiple developers).
-Visual studio crashes sometimes.
-Doesn't have good ELT functionality, though ofcourse one can just do SQL.
-Overall there's many small things that could be done to make development faster and the product is not definitely perfect, but one has to compare to offerings of other vendors, which are not 'perfect' from usability and performance standpoint eather.
Been using it for 5 years now.
Scalability not on the same level as with IBM Datastage.
Many customers are switching from IBM Datastage to SSIS. Think its the ease of use and license costs.