It DOES work, for sure. I can't speak for Rafael (or anyone else, for that matter) but the reasons why it's a non-starter for me is that 1) I shouldn't have to use a separate application that's written in a different language to do such things, 2) I find it more difficult to deploy packages than procedures from Dev to Test to UAT to Prod, 3) there's a lot that it can't do and so people end up writing stored procedures for anyway, 4) it typically doesn't automap and needs manual intervention. That kills me because we frequently end up importing from files that have dozens to hundreds of columns (yeah... I know... but not something in my control) and 5) because there is a lot it can't do, it promotes the "Tower of Babel" for code. The example I gave previously (get 1 file ready for import) used a combination of Perl, VB, Active X, and some other crazy thing (I don't recall the name of it) because multiple people worked on it over time and they didn't know how to do it in T-SQL nor did they want to touch the existing code written in a language they didn't actually know.
If you're good at SSIS and you get the right kind of performance out of it, then I can't fault you one bit because, according to MS, that's what it's there for. I just wish it wasn't needed. 😀