• 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