Jeff Moden - a TSQL guru on this site - swears by TSQL (and thus SSMS) and never uses SSIS for anything.
Reading text files --> bcp or bulk insert
Reading Excel --> OPENROWSET
And so on.
The biggest issue probably is security issues, as opc.three has pointed out.
But also row level error handling - which you can do easily in the SSIS dataflow - is problematic in TSQL. It requires a cursor, which will slow things down tremendously.
So, everything is possible in TSQL, but it will sometimes be harder and/or much slower.
Sometimes TSQL will be easier and much quicker (for example sorting, grouping and joining relational data).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP