You may have a big file or a large database table that you want to split up into different csv files. Instead of writing multiple queries for the different cases, you can use Conditional Split within SSIS to accomplish this task. There are also a multitude of other scenarios you can use it for.
Jim Salasek's SQL Server Blog 