The answer is: it all depends ;-)
The SqlDataSource is great for simple, small sites. They are easy to develop and understand, and quick to implement. But as soon as your site starts to grow, they are no longer the best option as you'll have SQL statements sprinkled all over the place. That's where I typically start using BOs with stored procedures under the hood.
Linq to SQL (or better yet, the Entity Framework) are a very compelling alternative, one I use more and more (especially with Code First in EF) and to a large extent they minimize the need for sprocs.
I would still use sprocs for intensive database processing or query logic.
Hope this helps,