We use SSIS for all of our ETL.  I would prefer that SSIS be used more for EL and not (T)ransform.  Setting up the SSIS to Extract and Load the data to a 'work' table, then using SQL to transform the data. In everything I've done so far in my career I haven't found any 'Transform' that I couldn't do in SQL.