SQL Server Integration Services (SSIS) is a very powerful graphical tool set belonging to the Microsoft BI Stack along with SQL Server. It supports a broad variety of data migration, integration and transformation tasks. With that, you will find a bunch of good reasons why to utilize this tool set. With Transact SQL (T-SQL) Microsoft provides with respect to transformation tasks an equivalent technique.
As so often: Discussions whether to solely utilize SSIS for the development of ETL processes in opposition to using T-SQL Stored Procedures, Stored Functions often end in controversial debates, as developers tend to stick to THEIR preferred choice. There is no general right or wrong. But depending on the requirements you will very likely find a sound combination of both SSIS and T-SQL that serves the requirements best. There may even be other helpful techniques, when thinking of ETL processes, such as Power Shel scripts for example.
With this series of articles, I want to address some issues/criteria, that are relevant for choosing the right technology. When comparing ETL technologies you often find benchmark tests. Frankly, there may be areas where these benchmark tests are relevant for choosing the right technology, but in most cases, they are not. Especially in times of sheer unlimited disk space, computing power and throughput.
The following issues/criteria are relevant for choosing the right extent of using either T-SQL and/or SSIS technology: