When should I use one versus the other? For instance, let's say I have 20 comma delimited, denormalized text files and I want to transform the data and load it into normalized SQL Server tables.
What are the size of the files? T/SQL is pretty fast, I've done million record imports in less than three seconds. Is this 'once' or 'scheduled'?
SSIS is for complicated scenarios. SSIS can schedule FTPs, external commands (such as ZIP and unZIP), branching paths on step failures, etc. Therefore it isn't as simple as importing data, it's the entire 'overnight data exchange process'.
Given that you have CSVs (presumably with column names) much of your work would appear to have been done already. If you don't need to filter out bad records you're just doing 'group bys' to generate validation fact tables.
More information is needed.
There has been a discussion on a similar subject on Linked in .
From what you said l would use TSQL.
One of the features l liked about SSIS was the ability to include some information regarding errors. So if the package crashed l knew where to look and had some idea why. Similar idea to this posting over kill for 20 CSV files. Just another way to solve a challenge.