Advantages of using SSIS packages over stored procedures?

I've lived in the land of stored procedure ETL for a multi-terabyte SQL Server data warehouse. This decision was made back in 2001 when .NET was 1.0, so VB6 was the programming language alternative, and SSIS wasn't around yet - it was DTS. I can tell you that there were advantages and disadvantages, like anything.

Some considerations:

1. If everyone on your team understands SQL, it's easy to dig into the stored procs. SQL is a widely known skill which may be a benefit if you have a lot of ETL writers/readers. You have to be more than a casual user of SSIS in order to understand what it's doing. The high level graphical flow is nice for documentation, but if someone needs to get into the guts, they'd better know SSIS well.
2. SQL is a pain to modularize. If you use UDFs, you are going to incur a huge performance hit. You'll write similar code in multiple places and you'll hate yourself for doing it, but often in ETL scenarios performance is king. SSIS will help you modularize and factor out your tasks.
3. Don't expect to be able to easily use source control with SSIS. SQL - no problem. SSIS uses awful XML files which can be checked in, but good luck diffing with previous versions to see what changed and when.
4. You need to think about your SPs in a modular way, even though it's hard to make them as modular as you'd like. Use temp tables to chunk up your processing. Put indexes on those temp tables before you use them. Don't try to do too much at once. Comment everything.
5. If you're using cursors, you're doing it wrong. Don't be afraid to chain in some external console app you wrote in the language of your choice to do some things SQL just wasn't cut out for.

BTW - after I left that company, they finally upgraded the database from SQL 2000 to 2008 and slowly moved from stored procs to SSIS. At my new company, we own SSIS but after using it we all agreed that our custom written .NET ETL is a better fit for our purposes. Everyone takes their own route. The decision has to balance maintenance and performance and the skill-set of your team and the skill-set of the job pool in your area.

I am in the middle of getting rid of our SSIS packages and using stored procedures. For us, stored procs are tremendously better:

1. They are far easier to maintain, we don't need bids, don't need to create projects and import packages into bids, so far fewer steps to make simple stored proc changes.
2. All of our current packages basically truncate data in a table, then repopulate from several other tables on the same server with direct mappings. Very easy Insert/select SQL to write.
3. They run much faster. We have no cursors, no looping structures, just straight SQL.
4. We don't have to spend all our time right-clicking and working in little bids windows trying to follow the flow of logic. We all know basic TSQL and that is sufficient for our tasks.

I would say it depends some on what you are doing. However, from my experience the room for improvement with SSIS packages is tremendous. We saw 10 fold improvements in our data warehouse environment when we took some of the heavy hitting stored procedures and put them in SSIS packages. The memory utilization of SSIS (in this situation anyways) made all of the difference.

I want to reiterate that it is important to know what you are doing. For example, a SQL statement will usally outperform a SSIS data-flow when the data transform is table-to-table on the same server.

The best bet it to pick a SP or two and create them in SSIS and test them both.

Seems like the answer for all SQL questions start with, It depends...

If your ETL is mostly E and L, with very little T, and if you can write your SPs so they don't rely on cursors, then going the SP-only route is probably fine.

For more complex processes, particularly those that involve heavy transforms, slowly changing dimensions, data mining lookups, etc, SSIS has three advantages.

First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone.

Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.

And third, SSIS lets you more easily interact with additional external sources, which can be very handy for things like data cleansing.