When you start SSIS development it seems rather easy to paste some tested t-sql into a box, QED! job done!
However, the real power of SSIS lies in the 2 areas where it out-performs raw t-sql.
1) Server linking: Because it uses a native format, grabbing data from a distant source and applying it somewhere else is quick and robust.
2) There are blinding-fast (in memory) replacements for the t-sql operations … GROUP BY (called the ‘Aggregate’ transformation), CASE (called ‘Conditional Split’), CAST (‘Data Conversion’), UPDATE (Derived Column), SOUNDEX (‘Fuzzy Lookup’), SORT (er called ‘sort’).